Forum Discussion
AverageIF eliminate & zeros
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!
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!
- You sir are a saint. The addition works perfectly, thank you!
