CODEWITHSUNDEEP
excel
M Koe
M Koe

Reputation: 39

Average If function - Exclude Zero?

I am using the following averageIf formula 

=AVERAGEIF('Backend Data - Aerospace'!D:D, "Total",'Backend Data - Aerospace'!E:E)

However, it is counting 0's and i do not want this. Is there a way i can exclude 0's from being calculated as part of the average?

Thanks

Upvotes: 3

Views: 567

Answers (1)

Felipe Alarcon
Felipe Alarcon

Reputation: 956

To exclude zero values, you'd use the criteria <>0.

=AVERAGEIFS('Backend Data - Aerospace'!E:E,'Backend Data - Aerospace'!D:D, "Total",'Backend Data - Aerospace'!E:E,"<>0")

If you need a formula that doesn't ignore empty cells or if you're still using Excel 2003, you can use a formula in the following format:

=SUM(range)/COUNTIF(range,"<>0")

Upvotes: 2

Related Questions