Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Negative Numbers to Zero
I've been searching around and can't seem to figure out how to write a
formula that will round a negative number to 0 correctly. I used the (MAX(0,A1) and it seemed to work from that cell but another cell pulls the value from that cell and it was still using the negative number to calculate. I need a formula that will round the negative number to Zero and then use the Zero from that roundup when other cells are referring to it. Does that make sense? Need help!! Thanks, Robbie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Negative Numbers to Zero
On Tue, 14 Jul 2009 13:26:02 -0700, Robbie
wrote: I've been searching around and can't seem to figure out how to write a formula that will round a negative number to 0 correctly. I used the (MAX(0,A1) and it seemed to work from that cell but another cell pulls the value from that cell and it was still using the negative number to calculate. I need a formula that will round the negative number to Zero and then use the Zero from that roundup when other cells are referring to it. Does that make sense? Need help!! Thanks, Robbie A1: your number B1: =max(0,a1) Have your other formulas either refer to B1; or in those cells, substitute max(0,a1) for a1 --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Negative Numbers to Zero
"Ron Rosenfeld" wrote: On Tue, 14 Jul 2009 13:26:02 -0700, Robbie wrote: I've been searching around and can't seem to figure out how to write a formula that will round a negative number to 0 correctly. I used the (MAX(0,A1) and it seemed to work from that cell but another cell pulls the value from that cell and it was still using the negative number to calculate. I need a formula that will round the negative number to Zero and then use the Zero from that roundup when other cells are referring to it. Does that make sense? Need help!! Thanks, Robbie A1: your number B1: =max(0,a1) Have your other formulas either refer to B1; or in those cells, substitute max(0,a1) for a1 --ron That worked. Only problem I have now is that the negative number still displays in A1 even though it is rounded to zero for purposes of calculation in other cells that refer to it. I can't change the formatting because sometimes the number won't be negative and we'll need to keep the formatting in that case. It's not the end of the world, but would be nice to have it fixed! Thanks again for your help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Negative Numbers to Zero
Hi,
I don't understand why you can't change the formatting - seems to me that the following custom format would do what you need: $#,##0.00;\0;\0 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Robbie" wrote: "Ron Rosenfeld" wrote: On Tue, 14 Jul 2009 13:26:02 -0700, Robbie wrote: I've been searching around and can't seem to figure out how to write a formula that will round a negative number to 0 correctly. I used the (MAX(0,A1) and it seemed to work from that cell but another cell pulls the value from that cell and it was still using the negative number to calculate. I need a formula that will round the negative number to Zero and then use the Zero from that roundup when other cells are referring to it. Does that make sense? Need help!! Thanks, Robbie A1: your number B1: =max(0,a1) Have your other formulas either refer to B1; or in those cells, substitute max(0,a1) for a1 --ron That worked. Only problem I have now is that the negative number still displays in A1 even though it is rounded to zero for purposes of calculation in other cells that refer to it. I can't change the formatting because sometimes the number won't be negative and we'll need to keep the formatting in that case. It's not the end of the world, but would be nice to have it fixed! Thanks again for your help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Negative Numbers to Zero
On Tue, 14 Jul 2009 14:55:01 -0700, Robbie
wrote: That worked. Only problem I have now is that the negative number still displays in A1 even though it is rounded to zero for purposes of calculation in other cells that refer to it. I can't change the formatting because sometimes the number won't be negative and we'll need to keep the formatting in that case. It's not the end of the world, but would be nice to have it fixed! Thanks again for your help! You could use a custom format to display all kinds of things in place of the negative number. For example: General;[Red]"negative value rounded to zero";General --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding to the nearest 25 including negative numbers?? | Excel Discussion (Misc queries) | |||
Rounding to the nearest 25 including negative numbers?? | Excel Discussion (Misc queries) | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
Rounding formulas for negative numbers | Excel Worksheet Functions |