#### If the result of a formula is negative make equal to zero

I do have a worksheet that calculates sales profits or losses quarterly in 3
different locations. For each quarter I do have a formula that gives me the
increase or decrease amounts during the last couple of years. For the total
result I need a formula that converts or ignores the negative results. Any
calculation result with a minus sign in the formula should be equal to zero,
so it's not included in the final  quaterly total.
Could somebody help me with it? Thanks!
 0
Nrippe (2)
5/25/2006 8:51:02 PM
excel.newusers 15348 articles. 2 followers.

5 Replies
318 Views

Similar Articles

[PageSpeed] 5

=IF(yourformula<0,0,yourformula)

Regards

Trevor

"Nrippe" <Nrippe@discussions.microsoft.com> wrote in message
news:70A91762-D216-4853-A569-55B039AFF1EB@microsoft.com...
>I do have a worksheet that calculates sales profits or losses quarterly in
>3
> different locations. For each quarter I do have a formula that gives me
> the
> increase or decrease amounts during the last couple of years. For the
> total
> result I need a formula that converts or ignores the negative results. Any
> calculation result with a minus sign in the formula should be equal to
> zero,
> so it's not included in the final  quaterly total.
> Could somebody help me with it? Thanks!

 0
Trevor9259 (673)
5/25/2006 9:07:09 PM
You could use the MAX function.

=MAX(A100,0)

This returns the larger of the two values.  So, if A100 is ever negative, 0
would be larger, and thus be what is returned.

HTH,
Elkar

"Nrippe" wrote:

> I do have a worksheet that calculates sales profits or losses quarterly in 3
> different locations. For each quarter I do have a formula that gives me the
> increase or decrease amounts during the last couple of years. For the total
> result I need a formula that converts or ignores the negative results. Any
> calculation result with a minus sign in the formula should be equal to zero,
> so it's not included in the final  quaterly total.
> Could somebody help me with it? Thanks!
 0
Elkar (111)
5/25/2006 9:11:02 PM
If you are totalling try:

=SUMIF(K1:K90,">"&0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Nrippe" <Nrippe@discussions.microsoft.com> wrote in message
news:70A91762-D216-4853-A569-55B039AFF1EB@microsoft.com...
>I do have a worksheet that calculates sales profits or losses quarterly in
>3
> different locations. For each quarter I do have a formula that gives me
> the
> increase or decrease amounts during the last couple of years. For the
> total
> result I need a formula that converts or ignores the negative results. Any
> calculation result with a minus sign in the formula should be equal to
> zero,
> so it's not included in the final  quaterly total.
> Could somebody help me with it? Thanks!

 0
sandymann2 (1054)
5/25/2006 9:30:50 PM
Thank you very much! It works perfectly.

"Trevor Shuttleworth" wrote:

> =IF(yourformula<0,0,yourformula)
>
> Regards
>
> Trevor
>
>
> "Nrippe" <Nrippe@discussions.microsoft.com> wrote in message
> news:70A91762-D216-4853-A569-55B039AFF1EB@microsoft.com...
> >I do have a worksheet that calculates sales profits or losses quarterly in
> >3
> > different locations. For each quarter I do have a formula that gives me
> > the
> > increase or decrease amounts during the last couple of years. For the
> > total
> > result I need a formula that converts or ignores the negative results. Any
> > calculation result with a minus sign in the formula should be equal to
> > zero,
> > so it's not included in the final  quaterly total.
> > Could somebody help me with it? Thanks!
>
>
>
 0
Nrippe (2)
6/9/2006 1:58:01 PM
You're welcome, thanks for the feedback

"Nrippe" <Nrippe@discussions.microsoft.com> wrote in message
news:FBBC3DD7-9F7C-4E5D-BEB7-8617B4C2D3C3@microsoft.com...
> Thank you very much! It works perfectly.
>
> "Trevor Shuttleworth" wrote:
>
>> =IF(yourformula<0,0,yourformula)
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Nrippe" <Nrippe@discussions.microsoft.com> wrote in message
>> news:70A91762-D216-4853-A569-55B039AFF1EB@microsoft.com...
>> >I do have a worksheet that calculates sales profits or losses quarterly
>> >in
>> >3
>> > different locations. For each quarter I do have a formula that gives me
>> > the
>> > increase or decrease amounts during the last couple of years. For the
>> > total
>> > result I need a formula that converts or ignores the negative results.
>> > Any
>> > calculation result with a minus sign in the formula should be equal to
>> > zero,
>> > so it's not included in the final  quaterly total.
>> > Could somebody help me with it? Thanks!
>>
>>
>>

 0
Trevor9259 (673)
6/12/2006 9:39:04 PM

Similar Artilces:

Limiting formulas based on filtering?
Is there an easy way to build a formula that will only take values into consideration when the auto format is used. So, if I have a table of data and a formula based on this table and I filter one of my data columns to some vause then I would like my formula to automatically update. Is this possible? Thank you, CH The SUBTOTAL function ignores rows that are hidden by an autofilter (and optionally, also rows that were hidden manually). It can perform a wide range of operations, including sums, averages, counts, standard deviations, products, maximums, minimums, etc. Check E...

formula for money additions & subtractions
I am trying to get a formula to calculate payroll deduction for cas register +/-. When an employee is over by \$10 or under by \$24, th difference comes out of thier check. Example: if somone is -25.00 for the month \$1.00 would come out of th check. If someone is +11.00 then \$1.00 would come out of the chec also. the +/- for the month is in one cell so i would like the formula to b able to read that cell and figure out if it is too much or little fo the deduction. this is the greatest sight that i have ever found and i hope someon can help me Thank you, Mik -- Message posted from http...

Formula in Marksheet #2
Hi all... I need help on a 'formula' for my students' marksheet. For them to pass the whole exam, a student MUST PASS 7 SUBJECTS from 3 groups. The grouping is like these : A) Must pass ALL 4 subjects in Group A - marks are in cells D7, F7, H7, J7 B) Must pass AT LEAST 2 OUT OF 3 subjects in Group B - marks are in cells L7, N7, P7 C) Must pass AT LEAST 1 OUT OF 2 subjects in Group C - marks are in cells R7, T7 Thank you so much something like? if count(d7,f7,h7,j7)<4,"bad","good") -- Don Guillett SalesAid Software dguillett1@austin.rr.co...

how to make all email address in my address book "safe sender"?
How to make all email address in my address book "safe sender"? That means I wish all the contact email address in my address book are "safe sender" and I wish to receive their incoming email without block as spam. Thank you. ay <nospam-jgy2001-nospam@hotmail.com> wrote: > How to make all email address in my address book "safe sender"? > > That means I wish all the contact email address in my address book > are "safe sender" and I wish to receive their incoming email without > block as spam. Version of Outlook? For OL 2003...

What is the formula for series: Jan-04, Feb-04, Mar-04, etc.?
Hi Kitty, =DATE(YEAR(A1),MONTH(A1)+1,1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "kitty" <kitty@discussions.microsoft.com> wrote in message news:2C586891-2883-47B9-A3DE-AF77D4A9799E@microsoft.com... > Hi kitty, One way of doing it is with the formula below: If you have a date is cell A1 (01/01/04) and you want cell B1 to sho you the following month insert in B1 the formula below. =EOMONTH(A1,0)+1 You may have to install the "add in" the ToolPak in order for th formula to work other wise you will get a #NAME?, error. Hope thi helps -- Fa...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

Leave Zero's in cell
Hi, when I put three zero's into a cell,.excel automatically changes it back to one zero. I want to keep the three zero's in the cell. Woiuld appreciate any advice please Regards Ivan Hi Ivan Either: Format the cell as Text (although then the numbers entered are text rather than genuine numerics) or format the cells to show leading zeroes (eg a custom format like 000) - note that this is simply for display purposes as the actual contents of the cell will be 0. Hope this helps1 Richard On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote: > Hi, > when...

making a decision tree using excel!
hello all, i`ve been trying to make a decision tree using excel, the problem i encounter is that i wish to put information in to b11 which is related to information in b3, the information in b3 is ether 0 or err and is based on a "y" or "n" being entered, does anyone know which formula to use which will print a true of false statement in b11 Hi Andrew, If you mean print "True" or "False" or your error b11: =Left(b3)="y" So you can use YES, Yes, yes, Y, y and get True everything else will show False Or to treat the error in ...

time formulas
Can anybody give me an advice Lets say i put in first column dime of departure and in second one time of arrival (of course i know have to insert the time ). In third column i get the time spent somwhere. So far everything is ok. The problem occ urs when i want the sum of all differences in the third column (rows are months dates). The autosumm formula wont work and the result is completely wrong. thanks miro Hi format the resulting cell with the custome format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany axiom wrote: > Can anybody give me an advice > > Lets say i put i...

How do I stop axis titles from overlapping with negative chart da.
The negative bar charts overlap with the category titles. I've tried offset, but it won't let me move the text more than '1000' units, and it still overlaps. Hi, You can find an explanation here. http://www.andypope.info/tips/tip002.htm Cheers Andy KarenNeedsHelp wrote: > The negative bar charts overlap with the category titles. I've tried offset, > but it won't let me move the text more than '1000' units, and it still > overlaps. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi Karen - Double click on the category titles o...

Asset with ZERO cost?
is it possible to keep record of asset with zero (acquistion) cost? these are assets either donated by others or assets where acquistion cost can not be identified. The assets are 'physically' there and we would like keep in record. we tried cost=1 and LTD depn=1 but the will make total assets cost different from our book. any advice? HF: You should be able to save the asset at zero cost. Can you not? Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL sorry, I can add asset at zero cost, but must the Depreciate to Date always equal Place In Service Date? thks &q...

Formula for excel example 555 x 15%
=555*0.15 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "sari" <sari@discussions.microsoft.com> wrote in message news:7B4BA8D5-04A4-4195-B749-DFEED0452A3A@microsoft.com... > ...

Extra comma and zero-length string
I have a 'simple' concatenation of 3 fields with a comma before the 3rd field: "FirstName LastName, Academic" I would like for the finished string to read; "Jane Smith, PhD" howevever, I've discovered many records have zero-length strings in the Academic field and are causing the comma to appear when the Academic field is blank, e.g: "Tom Jones," This is what I have, which works for most, but not all the records that the Title field appears blank: Name: [tbl_Investigator]![FirstName] & " " & [tbl_Investigator]![LastName] &am...

Pasting Formulas bug?
I seem to be having a strange problem with my Excel (11.2.5) for Mac. I have pasted formulas for years and never had an issue...until now. The formulas are simple (addition, subtraction, etc.). I copy the formula in a cell and paste it to a new one (I even do this with Paste Special...Formula). What appears in the spreadsheet is the value of the original cell, not the result of the "relative" formula -- even though the formula bar displays the correct "relative" formula. I can only get the correct value in the new cell if I go into the formula bar and press return at the...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

How to get Row # in formula?
I have several rows with the same formula but they are seperated, in some cases, by rows with other information to prevent me from just dragging a formula down to other rows. Ex. While in row 13: B13*C13+B13*D13+B13*E13 While in row 20: B20*C20+.... how can I write a formula to populate the "13" or "20" automatically for whatever row I am in? Thanks, Dave You may not be able to drag it, but if you copy row13 formula and go to row 20 it updates does it not? -- HTH Bob "DaveR" <DaveR@discussions.microsoft.com> wrote ...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

Lookup returning incorrect results
Anybody have an idea why I keep getting incorrect lookup results. In sheet 1, I have a list of "Sales Codes" in col A. In sheet 2, I have a list of data that with column A for "Sales Codes" and column B for associated "Sales Reps". I want to insert a column in sheet 1 that contains lookup formulas to pull the correct sales rep for each sales code . Both sheets are sorted in ascending order by Sales code. My lookup formula in sheet 1, Lookup col B: =LOOKUP(A2,Sheet2!\$A\$2:\$B\$4,Sheet2!\$B\$2:\$B\$4) The returned results from the above lookup formula are incorrect....

HELP!! How to formula the "sheet" link
Hi , I have a workbook contains 1+50 worksheet, I want to link each sheet number to sheet one, besides manually change the sheet # in the formula =SUM('sheet02'!\$H\$16:\$H\$21,'sheet02'!\$H\$26) =SUM('sheet03'!\$H\$16:\$H\$21,'sheet03'!\$H\$26) =SUM('sheet04'!\$H\$16:\$H\$21,'sheet04'!\$H\$26) =SUM('sheet05'!\$H\$16:\$H\$21,'sheet05'!\$H\$26) ..... up to sheet 50... Can someone help me to automatic it in anyway? Thank you very much!!!! Nicole Try a formula like the following: =SUM(INDIRECT("Sheet"&TEXT(ROW()-4+2,&...

Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text format. Is there anyway to have excel recognize that the result of the function is a formula and not text. Once I click on the cell, hit F2 to edit it and then enter, Excel realizes it's a formula and then calculates the value, Nope. The formula in the cell is going to be the UDF--not the what formula string looks like. You could add some more steps. Select the range (if more than one cell) Convert to values (edit|copy, edit|paste special|values) and finally edit|replace what: = (equal sign) with: = replace all And exce...

Currency makes me go Grrrr
Dear all, How do I change the currency sign? To get UK pounds I changed my computer settings to UK, as I could not find it under format cells, currency....surely there must be a better way. I have spreadsheets with \$ , � and would like to add Euros. Thanks, Danny On Thu, 13 Nov 2003 19:02:51 -0000, "Aardvark" <djurmann@hotmail.com> wrote: >Dear all, > >How do I change the currency sign? To get UK pounds I changed my computer >settings to UK, as I could not find it under format cells, >currency....surely there must be a better way. I have spreadsheets ...

Formula #64
I need to create an excel spreadsheet using a math equation that has one variable that is the denominator of a fraction. I need to be able to change the variable every row. Ie - starting variable is 3 and last variable is 101. Any tips would be greatly appreciated!! Thank you On Sat, 10 Sep 2005 15:31:07 -0700, "V" <V@discussions.microsoft.com> wrote: >I need to create an excel spreadsheet using a math equation that has one >variable that is the denominator of a fraction. I need to be able to change >the variable every row. Ie - starting variable is 3 and ...

Replacing Formulas
I am redoing a spreadsheet with several pages. In each page the cells refer to a sheet that contains a master list of items. THus in the cells it shows the item but really is a formula for the item to be retrieved from the master list. I would like to delete the master list. My question is: How do I get the items on each sheet to not become blank when I do so? Do I have to retype the items or is there some way of eliminating the formula and replacing it with the item name? I hope I have made myself clear. Thanks for any help. Bob Bob, select the cells, then Edit > Copy > Edit &...

Using formulas for pivot table
I know you have the count field but is there away to input a formula? For example customer ordered 25 cases and each case weighs 4 lbs and the end result would need to be total pounds ordered. Thanks! If you do the calculation in the pivot table you may not get the result that you expect. If possible, add a field to the source data, and calculate the order total there. Then, add the OrderTotal field to the pivot table's data area. tskb wrote: > I know you have the count field but is there away to input a formula? > For example customer ordered 25 cases and each case weighs 4 lbs...