Excel number format problem

[MisterC]

Howdy!
I'm having a problem with Excel's number formats.
I have the following data in my spreadsheet:

 12,904.48 	 (12,904.48)	      -   	        -        -
 46,408.24 	 (66,810.70)	 70,443.82 	 (50,041.36)    -   
 86,062.37 	 (81,216.21)	 (4,846.16)	       -   	[b][blue](0.00)[/blue][/b]

The last column is a total of the previous four. I want the zero values to show up as "-", and they all do except for the last cell. I've tried copying number formats and changing number formats, but still it shows up as (0.00)

Any suggestions??

 

[bluedragon2]

Try using the accounting format.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[rdeleon]

What format mask are you using?

The following format usually works for me. You can find it by selecting Format/Cells

Category: Custom

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

Rene'

 

[MisterC]

I am using the accounting format.

 

[MisterC]

I'm using the same format that I'm using in the cells that are showing "-".

 

[bluedragon2]

May I ask what the formula is in that cell?



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MisterC]

sure.

=SUM(B6:E6)

Thanks for your help!

 

[MisterC]

Wierd... If I change the number format of the "total" cell to a buncha decimal places, I get:

(0.000000000010913936421275100)

But I check the numbers in the cells that I'm adding and they are only 2 digit decimals.

 

[MisterC]

oops, that's weird, not wierd.

 

[MarkBeck]

Try to increase your decimal point places to determine if you have a fraction number that is less then Zero (0.000000156). if this is the case, add a "Round" argument to your function and restrict it to 2 (ROUND(A11,2). This would strip out the remaining fractions from the result, and turn it into a straight Nil

 

[MarkBeck]

This will do it for you;

=ROUND(SUM(B6:E6),2)

Mark

 

[MisterC]

Thanks Mark, Yes that will work.
But why should I have to use =ROUND on a sum of numbers that only have 2 decimal places???

 

[MarkBeck]

Take a look at the values in ALL the cells that this function evaluates (that is; either of B6:E6, or if they are formulas themselves, the cells THEY depend upon), you will see that at least one of them has a longer then 2 decimal point number.

Typically, this occurs when the dependant cell is a formula that results in a fraction, but due to the cell format it only display’s the two decimal points.

 

[MisterC]

NO,
I see that all of them are exactly two decimal places. They are not formulas, they are entered numbers.

 

[MisterC]

I Shoulda known. Some kinda mess about "floating point arithmetic may give inaccurate results"

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113&Product=xl2003