how to find errora in formulas that are calculating wrong

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ashbo - 09 Apr 2005 08:25 GMT

when formulas are set to add a column of currency, the result is more than
what it should be. when there is zero dollars in a cell, some are listed as
$ and others are listed as $ 0.00 and others are $0.00. the ones that
are $ 0.00 and $0.00 are the ones that are causing the error because when i
take them out, the column adds correctly. i've tried to change by going into
style and modifying but nothing seems to work. please help!!

Reply to this Message

Nick Hodge - 09 Apr 2005 14:55 GMT

It will be nothing to do with the format but will be the underlying data.
Formats 'mask' the data to display it as required so for example 0.0049
formatted to two decimals will show 0.00.

I suspect you are using currency and formulas are causing excel to divide
and multiple amounts resulting in numbers up to Excel's precision of 15
digits, by formatting them all to 0.00 you are 'truncating' the underlying
data.

Try using the ROUND function in your calculations to ensure all results are
'trimmed' to the correct number of decimals

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> when formulas are set to add a column of currency, the result is more than
> what it should be. when there is zero dollars in a cell, some are listed
[quoted text clipped - 6 lines]
> into
> style and modifying but nothing seems to work. please help!!

Reply to this Message