Forum Discussion

Copper Contributor

Jan 16, 2018

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 wha...

Silver Contributor

Jan 16, 2018

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!

Tracker (Updated).xlsx445 KB

Daniel Johnson
Copper Contributor
Jan 16, 2018
You sir are a saint. The addition works perfectly, thank you!