| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to get the complete list Excel Worksheet Functions | Excel Worksheet Functions | |||
| Worksheet Memory hog | Excel Discussion (Misc queries) | |||
| Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions | |||
| Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
| worksheet functions with dates | Excel Worksheet Functions | |||
Linear Mode
