Forum Discussion
Daniel Johnson
Jan 16, 2018Copper Contributor
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...
Haytham Amairah
Jan 16, 2018Silver Contributor
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!
- Daniel JohnsonJan 16, 2018Copper ContributorYou sir are a saint. The addition works perfectly, thank you!