Row of numbers adding incorrectly.

[Guest]

I have built a spread sheet that will add individual column of numbers. The
last row on each column is the total for that column. (Row 50).

The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 .
Row 50 being the Total for each column of numbers. The formula is to take
the total for each individual column and divide it by 1.06 then multiply that
by .06. I have formatted all numbers in each cell to be (numbers to 2
decimal places).

At the end of Row 51 (Lets say Column M) I have added all of the numbers in
Row 51. The Total in M51 does not equal the displayed numbers in each of the
cells. There is usually .01 to .05 cents difference.

I think I know what might be causing this error. It appears to be a
rounding error. I do not know how to fix this problem.

Any comments or suggestions are much appreciated.

 

[Nick Hodge]

Dale

Formatting only truncates the display, Excel still stores and uses in future
calculations the full value, hence the appearance of an incorrect result.

You can either use the ROUND function in your formulas to truncate the value
of use Tools>Options...>Calculation>Precision as displayed

I prefer the first route as it gives more control

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Guest]

Hello Nick,
Thanks for your suggestions. Option 2 worked well. I was wondering if you
could direct me on how to use the Round function?

 

[Dave Peterson]

You may want to read Excel's help for =round().

Then post back with your questions.

 

[Guest]

Thanks Dave --- I will give that a read.