Excel Percentage Averages

Closed
Twiggi - Aug 13, 2009 at 03:24 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 13, 2009 at 07:30 AM
Hello,
I have two columns of info, 1st column is of purchases and 2nd is of received values. Each Row is of a different site and I calculate the percentage of received vs. purchased. i.e =C1/B1 at the end I take an average of the percentages (which includes the zero %'s) ie. (10%+100%+0%+90%)/4=50%

Now the people using the report are complaining that I need to take an average of the total columns (which isn't mathematically sound to me) as this then excludes the zero's. Let me give an example
Column 1 is the names (let's use four rows for this example) Name 1, Name 2, Name 3, Name 4.
Column 2 is the purchase values - 20, 5, 0, 10
Column 3 is the receive values - 2, 5, 0, 9

If you take a percentage of each row you get 10, 100, 0, 90
if you average these percentages =AVERAGE(C1:C4) results in 50%

Now if you take the totals and then work out the average i.e. =SUM(B1:B4) results 35 and =SUM(C1:C4) results 16. Now if you average these two =C1/B1 results 45%

How do I show the values and include the zeros when formulating an average percentage?

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 13, 2009 at 07:30 AM
first 0/0 gives error. if here is no purchase and no recceipt that mean there is not transaction. that must be removed from the data base. So there are only three transactions.

secondly it is only what exactly you want average of percentages or do you want overall percentage. If it is former you can have your view. If it is second interpretation ratio of sums is better . there will be difference because

a1/b1+a2/b2+a3/b3 is not equal to (a1+a2+a3)/(b1+b2+b3)

in most cases ratio of the sums are used.
1