Adding numbers in a column that have 3 decimal places

[KimberlyC]

Hi
I have the following numbers in H8:H13:
1.000
1.000
0.088
0.751
0.252
1.000

When I add these up in cell H7, using =Sum(H8:H13), the answer is 4.090.
It should be 4.091
Why is this happening....and how can I stop it from doing that or how can I
adjust my formula to make it add these numbers correctly?
Thanks in advance for your help!!

Kimberly

 

[RagDyeR]

Are these numbers in H8:H13 the results of formulas, or did you key them in
manually, where you can select the individual cells and see the actual
number in the formula bar.

I'll bet this is a rounding issue.

See this link of John McGimpsey:

http://www.mcgimpsey.com/excel/pennyoff.html
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi
I have the following numbers in H8:H13:
1.000
1.000
0.088
0.751
0.252
1.000

When I add these up in cell H7, using =Sum(H8:H13), the answer is 4.090.
It should be 4.091
Why is this happening....and how can I stop it from doing that or how can I
adjust my formula to make it add these numbers correctly?
Thanks in advance for your help!!

Kimberly

 

[Ron Rosenfeld]

Hi
I have the following numbers in H8:H13:
1.000
1.000
0.088
0.751
0.252
1.000

When I add these up in cell H7, using =Sum(H8:H13), the answer is 4.090.
It should be 4.091
Why is this happening....and how can I stop it from doing that or how can I
adjust my formula to make it add these numbers correctly?
Thanks in advance for your help!!

Kimberly

The reason it is happening is because you don't really have the numbers you
posted in those cells. They are just being displayed rounded to three decimals
because that's what you have set for a number format.

Changing the number format does not change the contents of the cells.

So depending on whether you want to add the "real" numbers, or the numbers
rounded to three decimal places, your "adjustment" will be different.

Assuming you want to add the numbers rounded to three decimal places, you have
a few options.

1. Round whatever formula you are using to generate the numbers in H8:H13.
Instead of H8 containing =someformula, it would contain =ROUND(someformula,3).

2. Use an array formula to do the SUM after ROUNDing:

=SUM(ROUND(H8:H13,3))

To enter this array formula, first type or paste it into the cell/formula bar,
then, instead of just hitting <enter>, hold down <ctrl><shift> while hitting
<enter>. XL will place braces {...} around the formula.

3. You can set your entire worksheet to "precision as displayed" but this may
have unwanted consequences.


--ron

 

[KimberlyC]

The numbers are a result of a formula...
The formula is (for H8) G8/365

 

[Peo Sjoblom]

That's the reason, see RD's link