Question about a formula

  • Thread starter Thread starter Frank Kirk
  • Start date Start date
F

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.
 
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?
Click to expand...

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))
 
Assuming G4:G17 is not sorted in ascending order, you might try:
=IF(MIN(G4:G47)>0,GEOMEAN(G4:G17),AVERAGE(G4:G47))
Click to expand...

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