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?
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)
