sum not equating to parts

**tinkerbelle** · 07-04-2007, 10:06 AM

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

**tuph** · 07-04-2007, 07:53 PM

It would be much easier to identify the problem if you could zip and attach a copy of your Excel file.

**Bryan Hessey** · 07-05-2007, 01:20 AM

Originally Posted by tinkerbelle

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,

in your shown row 2 the figure at D2 is 71%, this is correctly 71.42857, the figure at H2 you show as 4%, this should be 3.571429

When you total such 'discrepancies' (for lack of a better term) via differing methods you can finish with a slight variance in your answers.

Temporarily set your columns to display some decimal places to show how the error manifests.

hth
---

**tinkerbelle** · 07-05-2007, 08:59 AM

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.

**EdMac** · 07-05-2007, 09:33 AM

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