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

=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

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

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

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

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