Average Question

[Cesar Zapata]

HI,

I have a question. I hope you can read the table below.
I created a Macro that will calculate the percentage on a imported .txt
file.
the formula I did in Col E to the th percentage is =(ColD-Col C)/Col D

the problem is the totals.

For the total in Col E I use the same formula. for the sum of Col C & Col D
as you can see I get 99.10%
If I use the Average funcion =AVERAGE(F2:F18) to get the total the I get
98.10%.

why is so much difference?


Col A Col B
Col C Col D Col E Col F
store1 MEAT 25 3010 99.17% 99.17%
store1 PRODUCE 174 32862 99.47% 99.47%
store1 SEAFOOD 14 1368 98.98% 98.98%
store1 CHEESE 9 3281 99.73% 99.73%
store1 DAIRY 48 10671 99.55% 99.55%
store1 BAKERY 141 4134 96.59% 96.59%
store1 PRE/FOODS 10 2442 99.59% 99.59%
store1 FLOWERS 33 190 82.63% 82.63%
store1 SUPPLEMENTS -3 1579 100.19% 100.19%
store1 BOOKS/CARDS/MAGAZINE 0 240 100.00% 100.00%
store1 BODY CARE 11 928 98.81% 98.81%
store1 COFFEE TEA BULK 17 397 95.72% 95.72%
store1 COFFEE BAR 0 153 100.00% 100.00%
store1 GROCERY 254 18005 98.59% 98.59%
store1 BULK GROCERY 51 5059 98.99% 98.99%
store1 FROZEN GROCERY 8 3238 99.75% 99.75%
store1 SUSHI 0 26 100.00% 100.00%
store1 total 792 87583 99.10% 98.10%



Thanks

 

[Bernie Deitrick]

Cesar,

You are confusing weighted averages and averages.

When you sum all the values and do the percentage, you are getting a
weighted average. Look at a case with just two items:

Item1 0 1 100%
Item2 99 99 0%

If you average the percentages, you would get 50%.

If you added them up first, you would get

Sum 99 100 99%

HTH,
Bernie
MS Excel MVP

 

[Cesar Zapata]

Bernie,


Thank you very much.

Cesar,

You are confusing weighted averages and averages.

When you sum all the values and do the percentage, you are getting a
weighted average. Look at a case with just two items:

Item1 0 1 100%
Item2 99 99 0%

If you average the percentages, you would get 50%.

If you added them up first, you would get

Sum 99 100 99%

HTH,
Bernie
MS Excel MVP

Col