spreadsheet off by a penny or two

**Mirawolf** · 09-29-2009, 05:20 PM

I have a spreadsheet that continues to have totals that are one or two pennies off. It seems that it is due to a cells formula '=SUM(C8*0.105)'. The multiplied decimal is 3 places, but I want the result to be in 2 decimal currency format. In different situations 'TRUNC(SUM(E8:E22), 2)' or 'ROUND(SUM(E8:E22), 2)' has done the trick. I have attached an example where the correct total for column E should be $1,290.06 and I always get the wrong total which is $1,290.04. Any assistance would be appreciated.

**Ron Coderre** · 09-29-2009, 05:42 PM

If you want the total to display the sum of the displayed values,
try this:

Please Login or Register  to view this content.

That formula rounds each value to 2 decimal places BEFORE summing them.

Does that help?

**Bozo** · 09-29-2009, 05:43 PM

There is a difference between formatting a cell to two decimal places and running calculations to two decimal places. In cell E18, the formula equates to 111.0741 and this is the value stored in the cell. You display it as 111.07 but for any calculation involving this cell, the value 111.0741 is used, NOT 111.07. Only using the ROUND() function will store the value of 111.07.
Hope this answers your question.

**masteff** · 09-29-2009, 05:44 PM

Do the rounding where you do the multiplication, not where you do the total... so make the formula in your rows =ROUND(C8*.105,2), then it works.

**JBeaucaire** · 09-29-2009, 05:44 PM

The solution is the same this time, too. The formula in E8 should be:
=ROUND(SUM(C8*$E$7),2)

If you explained why some of the values in column E are zero and have no formula, we could construct a formula that does that for you, too, so you could just copy E8 down the whole column.