CODEWITHSUNDEEP
google-sheets
Kurt Peek
Kurt Peek

Reputation: 57451

How to calculate an average in Google Sheets excluding outliers which are manually specified in a separate column?

I have a Google Sheet containing a column with values, Price/sqft, which I would like to average, but only including values for which an adjacent column, Outlier? does not contain the word yes.

I took a stab at it following the AVERAGEIF documentation, but I'm getting a 'divide-by-zero' error:

enter image description here

Another way to do it would be to create a separate column which contains the conditional value and compute a regular AVERAGE on that. Is this the way to go? Or is there a way to do this in one go using AVERAGEIF?

Upvotes: 3

Views: 2008

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34200

If you do use Averageif, you need to put the criterion range first and the average range last

=averageif(B2:B10,"<>Yes",A2:A10)

enter image description here

Upvotes: 1

Khyati Chandak
Khyati Chandak

Reputation: 66

You should use the formula =AVERAGEIFS(Q5:Q13,S5:S13,"<>yes")

Here, the syntax is like AVERAGEIFS(average_range, criteria_range1, criterion1)

screenshot

I hope this will be helpful.

Upvotes: 2

Related Questions