errors in worksheet functions
 

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
--
bill gras

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")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bill gras" wrote in message
...
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
--
bill gras

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bill gras" wrote in message
...
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
--
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
--
bill gras


"Bernard Liengme" wrote:

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bill gras" wrote in message
...
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
--
bill gras

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

"bill gras" wrote in message
...
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
--
bill gras


"Bernard Liengme" wrote:

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bill gras" wrote in message
...
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
--
bill gras