sum not equating to parts

Hi everyone, i am having difficulty in trying to understand the following:
File attached is a screen shot of my spreadsheet.

I cant get the sum of J2:J6 to equate to J7. in the example enclosed the sum of the individual values was 41.7 while the sum based on row 7 calculates as 43.23. I think it has something to do with the proportions.
You see that row 2 is 18% of the total while the other rows are 21%. Should I be applying a weighting somewhere ?

Thanks for your help

Hi guys, thanks for the reply, here is the zipped file.
My point is, and I am probably missing something fundamental here, the sum of column J is (ignoring the negative here) 41.70 but the total in J7 is 43.23.
So when someone reads across the total column they see a potential opportunity of 43.23 but an aggregate of the individual rows will sum to 41.70
Is it mathematically correct to expect the sum of the inidvidual rows here to add to the sum of the total, or is there something to do with weighting that I have misssed. As you can see from column L practice1 is 18% of the total but the other practices are 21%

Thanks for any help you can provide.

Hi Tinkerbelle

The problem is in the decimal parts that you can't see - formatting hides decimal places but they are still there and used in subsequent calculation.

If you use this in col J and copy down

=ROUND((I2*E2),0)

It comes out straight

Ed

Thanks EdMac, I thought it might have had something to do with the precision. Thanks

happy to have helped

Ed

Ed, having thought of this again, is the rounding function you suggested merely forcing the precision ? in other words, should be not expect the sum of each part to equal the bottom line exactly ?
Thanks for your help.

Hi Tinkerbelle,

If you set the format to, say 2 decimal places, the result is displayed as such but the value that is held is still the calculated value to 15 dps, which is the precision to which excel works.

If you ROUND the value then the value held is rounded to the number of places you specify.

If you have lots of calculation and then want to re-total parts back into the whole, it is almost impossible to get an exact match, you have to strike a balance between precision and acceptability.

I've just been back and revisited your sheet and, looking at it again, I am not sure of the logic.

Col H - does not seem to do anything. Col J - you are getting percentages of various figures but I'm not sure if J7 and J8 should equal each other as you are not comparing like with like.

Also if you look at Col K, you will see that the sum of the displayed values =102 due to the display rounding although if you expand the displayed values the reason is obvious.

This is probably as clear as Mud! But without knowing what outcome you are trying to achieve it's difficult to stop rambling on.

Does this help?

Ed