how to find errora in formulas that are calculating wrong

A

ashbo

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!!
 
N

Nick Hodge

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top