Zero Formats

[keith.evans]

Hi,

Why is it in some circumstances I get a positive zero and others a negative zero?

This has been doing my head in for a little while now!!

[smurfy]

Rounding? Display format to 2 decimal places, calculation goes to many more decimal places, and at some point, it will show as less than 0?

[keith.evans]

Hi smurfy. What I'm trying to achieve is for the text to turn red when it is no longer in credit or 'in the black'. I have achieved this in many formatted cells but the odd 1 or 2 don't display as I thought they should.

The format (I have just remembered I had) I use to fit the bill is attached along with the errant cell and same format.

I know I can set a format to round up/down and display a minus symbol but not to achieve the red text at the same time.

[smurfy]

Keith, sorry to take so long to come back to you. What I'm saying is that the actual value in the cell is still less than zero, despite only displaying zero to two decimal places.
Your number format doesn't change the value of the cell.
Try enclosing whatever formula you have there in a round function...
=ROUND((existing formula),2)
That should convert -0.000001 to 0.00 (I think).

[keith.evans]

Hi smurfy. If you hadn't mentioned the negative value I wouldn't have checked, so I did and found the cell contained a negative value of 0.00000000002514 !!! How the heck does that happen when I'm only dealing in numbers that only go to 2 decimal points???

Anyway, so far, I've changed the 'Totals' cells on the relevant sheet and the =ROUND format works fine. I'll have to think about whether or not I want (or need) to change all the other values in the other sheets!! My Totals sheet is far easier on the eye now so I'll call this a win smurfy!

Diolch yn fawr iawn!

[keith.evans]

Just to let you know smurfy, after checking all the other values (in the relevant docs) I found use of the =ROUND formula a few more times!

Thanks.....