worksheet sum function

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I found this the other day. See if this helps.

Use tools>options>calculations and precision as displayed
or use the round function. You probably suffer from
underlying values being not the same as displayed

Regards

Steve
 
Specifically, if some numbers that are being summed are calculated
rather than entered, they have more figures than you displayed (such as
1/3). Formatting the display does not impact the value in the cell,
therefore is you only want a given number of decimal places (such as 2
for currency) then you have three options

Explicitly round the results
=ROUND(formula,2)
in place of each formula used in the sum

Explicitly round the inputs to the SUM() function
=SUMPRODUCT(ROUND(range,2))
or
=SUM(ROUND(range,2))
The latter must be array entered (Ctrl-Shift-Enter)

Set "Precision as Displayed" per Steve's direction. This will impact
all calculations, not just the sum, so be careful that it does not have
unintended consequences.

Jerry
 
Back
Top