Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Testing for error : was 2nd Occurence
I have my statement working OK, thanks to all. =INDEX($E$1:$E$62933,SMALL(IF($B$1:$B$62933=B62938 ,ROW($E$1:$E$62933)),$F$2)) When the name is not found in the array, I have a #NUM error value. Is there a way to test for error, and replace with a zero to the above statement please? As an aside, I had the devils own trouble getting sense from the above statement, when I used the array E2:E62933 (I had a header row, and felt I not need to include in the array) when I used E1:E62933 everything worked fine. Why is that? Thankyou. Rodney |
#2
|
|||
|
|||
The error part comes from the SMALL function so you need to test there
=IF(ISERROR(Small(----)),0,your full formula) Regards, Peo Sjoblom "Rodney" wrote: I have my statement working OK, thanks to all. =INDEX($E$1:$E$62933,SMALL(IF($B$1:$B$62933=B62938 ,ROW($E$1:$E$62933)),$F$2)) When the name is not found in the array, I have a #NUM error value. Is there a way to test for error, and replace with a zero to the above statement please? As an aside, I had the devils own trouble getting sense from the above statement, when I used the array E2:E62933 (I had a header row, and felt I not need to include in the array) when I used E1:E62933 everything worked fine. Why is that? Thankyou. Rodney |
#3
|
|||
|
|||
=IF(ISERR(INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$629 33=B62938,ROW($E$2:$E$6293
3)),$F$2))),0,INDEX($E$2:$E$62933,SMALL(IF($B$2:$B $62933=B62938,ROW($E$2:$E$ 62933)),$F$2))) All on one line, watch out for wordwrap...... Vaya con Dios, Chuck, CABGx3 "Rodney" wrote in message ... I have my statement working OK, thanks to all. =INDEX($E$1:$E$62933,SMALL(IF($B$1:$B$62933=B62938 ,ROW($E$1:$E$62933)),$F$2) ) When the name is not found in the array, I have a #NUM error value. Is there a way to test for error, and replace with a zero to the above statement please? As an aside, I had the devils own trouble getting sense from the above statement, when I used the array E2:E62933 (I had a header row, and felt I not need to include in the array) when I used E1:E62933 everything worked fine. Why is that? Thankyou. Rodney |
#4
|
|||
|
|||
Not necessary to drag the whole formula into the error check, the num error
comes only from the SMALL part -- Regards, Peo Sjoblom "CLR" wrote in message ... =IF(ISERR(INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$629 33=B62938,ROW($E$2:$E$6293 3)),$F$2))),0,INDEX($E$2:$E$62933,SMALL(IF($B$2:$B $62933=B62938,ROW($E$2:$E$ 62933)),$F$2))) All on one line, watch out for wordwrap...... Vaya con Dios, Chuck, CABGx3 "Rodney" wrote in message ... I have my statement working OK, thanks to all. =INDEX($E$1:$E$62933,SMALL(IF($B$1:$B$62933=B62938 ,ROW($E$1:$E$62933)),$F$2) ) When the name is not found in the array, I have a #NUM error value. Is there a way to test for error, and replace with a zero to the above statement please? As an aside, I had the devils own trouble getting sense from the above statement, when I used the array E2:E62933 (I had a header row, and felt I not need to include in the array) when I used E1:E62933 everything worked fine. Why is that? Thankyou. Rodney |
#5
|
|||
|
|||
Thank you gentlemen, both.
There is a palpable joy when finally, something that seemed utterly obtuse, works. :) I think I shall purchase for my wife, some flowers......... |
#6
|
|||
|
|||
You're welcome Rodney...........
I think I shall purchase for my wife, some flowers......... Thats a wonderful idea.........and you might throw in a hug for good measure....... Vaya con Dios, Chuck, CABGx3 "Rodney" wrote in message ... Thank you gentlemen, both. There is a palpable joy when finally, something that seemed utterly obtuse, works. :) I think I shall purchase for my wife, some flowers......... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation Testing in an Excel application. | Excel Discussion (Misc queries) | |||
testing more than one variable in an if statement | Excel Discussion (Misc queries) | |||
Using VLOOKUP,what if I have more than one occurence of my Lookup. | Excel Worksheet Functions | |||
removing duplicates testing in 2 coloms | Excel Worksheet Functions | |||
I need to count the occurence of text in a range of cells (which . | Excel Worksheet Functions |