Working out Average of percentages ignoring 0.00%

[Rob_UK]

Hi guys, newbie here!!!

Also I am guessing a fairly simple question!

I have an auto completing spreadsheet tracking the percentage of sales to incorporate x,y, and z. On days where the agent is not in or does not sell the cell shows 0.00%. At the bottom of the list I want to display the "average" of the percentages listed, excluding the 0.00%'s ?!?!?!

The list is as below:

B C
DATE PERCENTAGE

1st 0.00% (row 6 )
2nd 0.00%
3rd 0.00%
4th 0.00%
5th 0.00%
6th 0.00%
7th 3.23%
8th 0.00%
9th 0.00%
10th 0.00%
11th 0.00%
12th 4.00%
13th 9.68%
14th 0.00%
15th 0.00%
16th 0.00%
17th 3.70%
18th 0.00%
19th 0.00%
20th 0.00%
21st 6.25%
22nd 3.45%
23rd 9.38%
24th 0.00%
25th 0.00%
26th 2.99%
27th 0.00%
28th 0.00% (row 36)

AVERAGE TOTAL ????????

Can anybody help me??? Thanks in advance.......

[VBA Noob]

Try

=SUM(B6:B33)/SUMPRODUCT(--(LARGE((B6:B33),ROW(INDIRECT("1:"&COUNT(B6:B33))))<>0))

or this array formula Ctrl + shift + enter

=AVERAGE(IF(B6:B33>0,B6:B33))

VBA Noob

[Richard Schollar]

Hi

If you don't have any negatives (which I assume you don't) then:

=SUMIF(C6:C36,">0")/COUNTIF(C6:C36,">0")

Hope this helps!

Richard

[Rob_UK]

WOW - That was a fast reply! PLUS your answer worked brilliantly!!

For anyone reading this the dates were in column B and the data in column C so you need to change the B's to C's in the formula.


Thanks so much

Your new No.1 fan!

[Rob_UK]

Hi again!

Dont want to push my luck but......

I have a line graph showing these percentages but obviously it dips when it reads a 0.00% giving a scewed result. How can I tell it to ignore 0.00% . I once diod something with NA# or similar but am guessing as there is a formula in each cell this will be difficult?

Thanks for anyone that has a clue what im talking about!!

[Richard Schollar]

Rob

Graphs don't plot #N/A values (which is convenient) so if your 0% are derived by a formula like:

=A1/B1 or some such, you can 'force' a #N/A value from this by amending the formula thus:

=IF(A1=0,NA(),A1/B1)

Now this will sort the graph but it will make your table of data look horrible, so one option would be to use conditional formatting to hide the #N/As (possibly by formatting the font colour to be white).

A potentially better solution would be to base the graph on another column (eg a hidden one) which converts the 0s to #N/A but leaves non-zero numbers as they are.

Hope this helps!

Richard