Google docs does not have AVERAGEIF and LOOKUP functions

Showing 1-4 of 4 messages
Google docs does not have AVERAGEIF and LOOKUP functions wfeg 7/2/09 6:54 PM
My uploaded spreadsheet uses AVERAGEIF and LOOKUP functions, but Google spreadsheet does not support these funcftions.

Is Google docs intending to implement more functions, including AVERAGEIF and LOOKUP functions? I have other spreadsheets that require also that SUMIF function.

Wfeg
Re: Google docs does not have AVERAGEIF and LOOKUP functions ahab 7/3/09 1:44 AM
Google has the FILTER function which allows you to create formulas with the same behaviour as AVERAGEIF, LOOKUP and SUMIF

E.g. 
Excel =AVERAGEIF( Range, Criteria, Sum Range) , e.g.  =AVERAGEIF( B2:B10, ">0", A2:A10)
Would be in Google Docs spreadsheet =AVERAGE(FILTER( Sum Range, Range test Criteria)) , e.g. =AVERAGE( FILTER( A2:A10 ; B2:B10>0 ))

Excel =SUMIF( Range, Criteria, Sum Range) , e.g.  =SUMIF( B2:B10, ">0", A2:A10)
Would be in Google Docs spreadsheet =SUM(FILTER( Sum Range, Range test Criteria)) , e.g. =SUM( FILTER( A2:A10 ; B2:B10>0 ))

Excel =Lookup( value, lookup_range, result_range ) , e.g. =LookUp( 10 ; B2:B10 ; A2:A10 )
Would be in Google Docs spreadsheet =FILTER( result_range ; lookup_range test value ) , e.g. =FILTER( A2:A10 ; B2:B10=10 ) 
Note FILTER can return multiple values, so you can restrict this to one like this =INDEX( FILTER( A2:A10 ; B2:B10=10 ) ;1 ; 1)

FILTER is very versatile...
Re: Google docs does not have AVERAGEIF and LOOKUP functions bmoore16 7/9/09 10:12 AM
ahab,

I need to do an AverageIf comparable in GoogleDocs but how does it look if I need to do multiple IFS or FILTERS?  ....or is it possible in GoogleDocs
Re: Google docs does not have AVERAGEIF and LOOKUP functions ahab 7/9/09 11:38 AM
If you mean you need more conditions, this is simple using FILTERs, just add aother condition; all condition resuls are automatically logically ANDed with each other.
 
E.g.
Average the numbers in A2:A10 where the numbers in B2B10 are > 0 and <=10 :
=AVERAGE( FILTER( A2:A10 ; B2:B10>0 ; B2:B10<=10 ))
 
Or do you mean something different?