Addition errors with Decimal Places

[Kevin M]

Hi,

I am working on some costing spreadsheets and am having some trouble. I
have 4 cells that are calculated with formulas for different sheets, the
results are:

cell A1 = $323,608.67
cell A2 = $2,992,499.72
cell A3 = $2,414,995.98
cell A4 = $1,928,911.98

when I sum these numbers in A5 I get $7,660,016.35

now, for display purposed I don't want to show decimals, so when I cange
everything to display no decimal places I get

cell A1 = $323,609
cell A2 = $2,992,500
cell A3 = $2,414,996
cell A4 = $1,928,912

and cell a5 = $7,660,016

now I understand whats happening, excel is calculating the actual numbers
with 2 decimal places, and rounding down to $7,660,016. This is correct, but
visually, once all 4 numbers are rounded, if I was to sum the 4 cells, cell
a5 should be $7,660,017.

Can someone explain to me how to make cell a5 calculate the visual numbers
with no decimal points from cells a1-a4 and not the actual numbers.

Thanks,
Kevin

 

[David Biddulph]

=SUM(ROUND(A1:A4,0)) as an array formula (Control Shift Enter).

 

[Peo Sjoblom]

Use precisions as displayed under tools>options>calculation, of course the
values will change for ever and it would be less exact

--


Regards,


Peo Sjoblom

 

[T. Valko]

Use rounding in the source formulas:

A1:A4 =ROUND(your_formula,0)

 

[Ron Rosenfeld]

Hi,

I am working on some costing spreadsheets and am having some trouble. I
have 4 cells that are calculated with formulas for different sheets, the
results are:

cell A1 = $323,608.67
cell A2 = $2,992,499.72
cell A3 = $2,414,995.98
cell A4 = $1,928,911.98

when I sum these numbers in A5 I get $7,660,016.35

now, for display purposed I don't want to show decimals, so when I cange
everything to display no decimal places I get

cell A1 = $323,609
cell A2 = $2,992,500
cell A3 = $2,414,996
cell A4 = $1,928,912

and cell a5 = $7,660,016

now I understand whats happening, excel is calculating the actual numbers
with 2 decimal places, and rounding down to $7,660,016. This is correct, but
visually, once all 4 numbers are rounded, if I was to sum the 4 cells, cell
a5 should be $7,660,017.

Can someone explain to me how to make cell a5 calculate the visual numbers
with no decimal points from cells a1-a4 and not the actual numbers.

Thanks,
Kevin

This formula must be **array-entered**:

=SUM(ROUND(A1:A4,0))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Sean Timmons]

in Cell B1, do =ROUND(A1,0) and copy across to B4

sum in B5.

 

[Jacob Skaria]

Hi Kevin

--Instead of just SUM() use the below formula which will round the
individual numbers and do a SUM()

=SUMPRODUCT(ROUND(A1:A4,0))

--There is a setting in Excel under Tools>'Calculation' tab>'Precision as
displayed'..But do remember this will affect all calculations.

If this post helps click Yes