Sum Function

N

Nenagh

Hi,

I have a spreadsheet invoice were most of the line charges are formulas
looking up other spreadsheets.

There is a sum function for the total of the column, however when it adds:

$326.08 and $32.61 the total given is $358.61. It should be $385.69.

All the cells that are being added have been formated to 2 decinal places.

How do i get the correct answer?
 
J

Joerg Mochikun

The cells to be added need not only be formatted to 2 decimal places, they
also have to be rounded (see for ROUND function), so that that Excel
calculates values exactly as they are being displayed.

Joerg
 
J

Joerg Mochikun

If you can't or don't want to apply the ROUND function to the values used
for the summation, you can use ROUND in your SUM function.
Example:
A1: 326.084 (displays as 326.08)
A2: 32.614 (displays as 32.61)
A3: =SUM(A1:A2) (displays as 358.70 <= you want this to be displayed as
358.69)

You could use an array formula in A3 to get the desired result:
=SUM(ROUND(A1:A2,2))

You need to input the formula with Ctrl+Shift+Enter, not just Enter. This
will put curly brackets around the formula.

Cheers,

Joerg
 
Top