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