# AVERAGEIF IN GOOGLE DOCS

October 16, 2012 Leave a comment

There is no function called Averageif in google docs spreadsheet as on October 2012. The average function return the arithmetic mean of a given data. If you want to eliminate certain value in the data and calculate the average of the remaining number the averageif comes into picture. We will using the average function only but we can manipulate in such a way to get the desired results

**Example 1:**

** **

The data is from Cell G2 to G11 containing 10 number if we want to calculate the average of numbers the formula will be =AVERAGE(G2:G11) which is equal to 138.4.

Suppose if we want to eliminate **0** (ZERO) while calculating the average then the formula will be

=ARRAYFORMULA(AVERAGE(IF(G2:G11>0;G2:G11)))

the result will be 173 as shown the above image.

Example 2:

** **

in the above data set we want to calculate the average of the number which are between 150 and 160.

** **

to achieve this we have used the following formula in cell B12

=ArrayFormula(AVERAGE(IF(B2:B11>=150;IF(B2:B11<=160;B2:B11))))

** **

similarly the alternatives will be

=ArrayFormula(AVERAGE(IF((B2:B11>=150)*(B2:B11<=160);B2:B11)))

=AVERAGE(FILTER(B2:B11;(B2:B11>=150)*(B2:B11<=160)))

or

=AVERAGE(QUERY(B2:B11;”select B where B >=150 and B <=160″))

the above formula uses single IF statement whereas the previous formula uses the nested IF function which sometimes may become complicated to understand.

Similarly we can add any number of multiple conditions to calculate the average.