adding up sum of several products is off

[Kendra]

I'm trying to set up a payroll spreadsheet.
I'm trying to sum up a series of products & even though I
have decimal set out 2 places, excel still sums everything
as though there is 3 places after the decimal. Therefore,
throwing me off by one penny when I do an autosum of the
products. How can I stop this from happening?

 

[J.E. McGimpsey]

Unless you have the Tools/Options/Calculation Precision As Displayed
checkbox checked (which may be an option for you), the format you
display has nothing to do with the number that XL stores in the cell
and uses for calculation.

Either use Precision as displayed (which may affect your other
calculations), or use ROUND():


A1: =ROUND(<some calculation>,2)
B1: =ROUND(<some calculation>,2)
C1: =ROUND(<some calculation>,2)
D1: =SUM(A1:C1)

Note that this will work well for additions, subtractions and
multiplications, but you should be aware that division may still
give you discrepancies:

If <some calculation> is, say J1/3 where J1 = 1.00, then the
result in D1 will be 0.99, not 1.00. You can compensate somewhat by
using:

C1: = J1-SUM(A1:B1) ==> 0.34

However, you need to think about this design, since, if the
calculation were instead J1/7 the result for the same technique over
seven cells:

G1: =J1-SUM(A1:F1) ==> 0.16

i.e., the discrepancies get larger (0.02 vs. 0.01). That's
unavoidable to some extent, but you need to plan for it.

 

[Kendra]

Thank you so much for responding. I used the precision as
displayed & it seems to work great. I'll keep an eye on
it to make sure it doesn't throw anything else off.