Rounding Negative Numbers to Zero

  • Thread starter Thread starter Robbie
  • Start date Start date
R

Robbie

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
 
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
Click to expand...

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
 
Ron Rosenfeld said:
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
Click to expand...


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!
 
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
 
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!
Click to expand...

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
 
Back
Top