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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 03:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com