Addition errors with Decimal Places
 

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 a

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

Addition errors with Decimal Places
 

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

"Kevin M" wrote in message
...
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 a

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

Addition errors with Decimal Places
 

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

--


Regards,


Peo Sjoblom


"Kevin M" wrote in message
...
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 a

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

Addition errors with Decimal Places
 

Use rounding in the source formulas:

A1:A4 =ROUND(your_formula,0)

--
Biff
Microsoft Excel MVP


"Kevin M" wrote in message
...
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 a

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

Addition errors with Decimal Places
 

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

sum in B5.

"Kevin M" wrote:

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 a

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

Addition errors with Decimal Places
 

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
---------------
Jacob Skaria


"Kevin M" wrote:

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 a

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