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?

Configuration: Windows XP Internet Explorer 8.0