errors in worksheet functions

B

bill gras

I have 3 worksheet fuctions that show errors eg:

=AVERAGE(LARGE(Z32:Z36,{1,2,3,4,5})) returns #NUM! when cell "Z" is blank

=SMALL(AE32:AE300,2) returns #NUM! when cell "AE" is blank

=VLOOKUP(AH39,AE32:AG300,3,FALSE) returns #NUM! when cells "AH" contains
#NUM! ,"AE" is blank , "AG" is a number (2)

can some one tell me how to turn #NUM! into a blank cell ? Please.

Thank you all
regards bill gras
 
B

Bernard Liengme

From my experimentation, you must mean when COLUMN Z is blank; actually when
the range has less than 5 numerical values. I assume you really have a
larger range otherwise why the LARGE sine Z32:Z36 has only 5 values!
Try this =IF(COUNT(Z32:Z36),AVERAGE(LARGE(Z32:Z36,{1,2,3,4,5})) ,"Not
enough")
 
B

Bernard Liengme

That should be
=IF(COUNT(Z32:Z36)>=5,AVERAGE(LARGE(Z32:Z36,{1,2,3,4,5})) ,"Not
enough")
 
B

bill gras

Hi Bernard

Yes I ment colums insted of cells in all 3 formulas
The formula you gave me works perfect thank you for that.
Can you do something with the other 2 functions ?
Thanks
 
B

Bernard Liengme

Just mimic the first one.
=IF(COUNT(AE32:AE300)>=2,SMALL(AE32:AE300,2)."TOO FEW")
=if(ISERROR(VLOOKUP(AH39,AE32:AG300,3,FALSE)),"SOMETHING
WRONG",=VLOOKUP(AH39,AE32:AG300,3,FALSE))
or
=if(ISERROR(VLOOKUP(AH39,AE32:AG300,3,FALSE)),"",VLOOKUP(AH39,AE32:AG300,3,FALSE))
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

bill gras said:
Hi Bernard

Yes I ment colums insted of cells in all 3 formulas
The formula you gave me works perfect thank you for that.
Can you do something with the other 2 functions ?
Thanks
Click to expand...
 
Top