Excel Percentage Averages [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!