Question about a formula

[Frank Kirk]

I want to search a column to test for a number that's less than 0 &
then perform one of two actions. Here's the formula I'm currently using:

=IF(ISNA(LOOKUP("<=0",G4:G17)),GEOMEAN(G4:G17),AVERAGE(G4:G17))

Would anyone tell me if there's a better or more efficient way of doing
this?

Many thanks.

 

[Ken Mintz]

I want to search a column to test for a number that's less than 0 &
then perform one of two actions.  Here's the formula I'm currently using:

=IF(ISNA(LOOKUP("<=0",G4:G17)),GEOMEAN(G4:G17),AVERAGE(G4:G17))

Would anyone tell me if there's a better or more efficient way of doing
this?

Obviously you cannot use "<=0" for the LOOKUP value. Well, unless you
want to the text "<=0" in G4:G17 ;-).

LOOKUP requires that G4:G17 is sorted in ascending order. If that is
the case, it would be sufficient for you write:

=IF(G4>0,GEOMEAN(G4:G17),AVERAGE(G4:G47))

Assuming G4:G17 is not sorted in ascending order, you might try:

=IF(MIN(G4:G47)>0,GEOMEAN(G4:G17),AVERAGE(G4:G47))

 

[Frank Kirk]

Assuming G4:G17 is not sorted in ascending order, you might try:

=IF(MIN(G4:G47)>0,GEOMEAN(G4:G17),AVERAGE(G4:G47))

That did it! Thanks for a very clean, efficient solution. I owe you a
martini.