errors in worksheet functions

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

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

"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:15CA5678-EFEB-490C-B762-C259D2490EDE@microsoft.com...
>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

[Bernard Liengme]

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" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:OnZReOFwFHA.1256@TK2MSFTNGP09.phx.gbl...
> 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" <billgras@discussions.microsoft.com> wrote in message
> news:15CA5678-EFEB-490C-B762-C259D2490EDE@microsoft.com...
>>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
>
>

[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" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:OnZReOFwFHA.1256@TK2MSFTNGP09.phx.gbl...
> > 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" <billgras@discussions.microsoft.com> wrote in message
> > news:15CA5678-EFEB-490C-B762-C259D2490EDE@microsoft.com...
> >>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
> >
> >
>
>
>

[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" <billgras@discussions.microsoft.com> wrote in message
news:815ECEFB-C181-44C9-86AD-094BD1B8B48E@microsoft.com...
> 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" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> news:OnZReOFwFHA.1256@TK2MSFTNGP09.phx.gbl...
>> > 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" <billgras@discussions.microsoft.com> wrote in message
>> > news:15CA5678-EFEB-490C-B762-C259D2490EDE@microsoft.com...
>> >>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
>> >
>> >
>>
>>
>>