Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Changing negative numbers to zero
Hi all,
Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet |
#2
|
|||
|
|||
Hi,
If those numbers (negative as well as positive) are the outcome of a formula, you may modify the formula as, =if(formula<0,0,formula) Regards, B. R. Ramachandran "Chet T (Chet T)" wrote: Hi all, Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet |
#3
|
|||
|
|||
Hi,
I apologize for my earlier reply to your posting. I didn't notice that do not want to use the IF function to change negative numbers to zero. Sorry. Regards, B. R. Ramachanrdan "Chet T (Chet T)" wrote: Hi all, Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet |
#4
|
|||
|
|||
How about just:
=max(0,youroriginalformulahere) If your formula returns -3, then 0 is larger and will be returned. If your formula returns +33, then that's bigger than 0. So you'll see 33. Chet T wrote: Hi all, Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet -- Dave Peterson |
#5
|
|||
|
|||
Thanks Dave!
That did it. Chet On Thu, 06 Oct 2005 19:01:54 -0500, Dave Peterson wrote: How about just: =max(0,youroriginalformulahere) If your formula returns -3, then 0 is larger and will be returned. If your formula returns +33, then that's bigger than 0. So you'll see 33. Chet T wrote: Hi all, Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet -- Dave Peterson |
#6
|
|||
|
|||
Thanks for the response B.R.
The formula already hase one if statement in it and I'm not sure that I am good enough at excel yet to try to deal with two if statements. Dave's =MAX(0,formula) response did the trick. Thanks again. Chet On Thu, 6 Oct 2005 15:25:02 -0700, "B. R.Ramachandran" wrote: Hi, I apologize for my earlier reply to your posting. I didn't notice that do not want to use the IF function to change negative numbers to zero. Sorry. Regards, B. R. Ramachanrdan "Chet T (Chet T)" wrote: Hi all, Is there any way to automatically change any negative numbers in a cell to zero? For a spend down spreadsheet that I am developing, any negative numbers will be zero. I could not find a format or function that would do it. I would like to avoid the =if(.......0,......,0) route because the formula in the cell already contains an if argument and it is already too complicated. Would an array formula work? I haven't worked with them too much. Any advice is appreciated. TIA Chet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions | |||
Can I calculate negative numbers to = zero in excel? | Excel Discussion (Misc queries) | |||
Using Replace to Format Negative Numbers | Excel Discussion (Misc queries) | |||
Negative numbers | Excel Discussion (Misc queries) | |||
Putting numbers in excel without it changing into a formula? | Excel Worksheet Functions |