AverageIF eliminate & zeros

[Daniel Johnson]

Good morning everyone,

I need help finishing a formula. I have been able to make it eliminate duplicates but require it to also eliminate zeros (as it affects the average) I have an idea of what the addition needs to be but cannot seem to put it all together.

{=AVERAGE(IF(FREQUENCY(IF(Q7:Q500<>"",MATCH(Q7:Q500,Q7:Q500,0)),ROW(Q7:Q500)-ROW(Q7)+1),Q7:Q500))}

Thanks in advance!

[#2]

Daniel,

Use this array formula:

=AVERAGE(IF(IF(FREQUENCY(Q7:Q500,Q7:Q500),Q7:Q500)=0,"",IF(FREQUENCY(Q7:Q500,Q7:Q500),Q7:Q500)))

But you have to press Ctrl+Shift+Enter to enter this formula, and to force it to deliver the right result!

[#3]

You sir are a saint. The addition works perfectly, thank you!