Thursday, September 20, 2012

Alternate for Excel function AVERAGEIFS in Google Spreadsheet

Question:

I have a spreadsheet that works perfecctly in excel but when I upload to google one field shows an error "Name".

The formula I'm using is: =AVERAGEIFS(F3:F500,G3:G500,"w")
This works in excel and whait it does is looks in column G for a cell with a w in it, it then works out the average for the figures in column F when next to a "w" in G



Solution:


Here are some ways by which you can achieve the same thing in Google Spreadsheet, that can be acheived by AVERAGEIFS in EXCEL:

=SUMIF(G3:G;"w";F3:F)/COUNTIF(G3:G;"w") 

OR

=AVERAGE(FILTER(F3:F;G3:G="w")) 

OR

=AVERAGE(QUERY(F3:G;"select F where G = 'w'")) 


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

1 comment:

  1. The second formula works perfectly! It can also be transposed and works the same way for row-based conditional averaging. Great solution!!

    ReplyDelete