Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error
Is there any way to get #NUM! error to be hidden? The formula works great but
for every instant returned that does not have a value iit shows this error. Formula used: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) then ctl shift enter Names in Column A Companies in Column B Name to look up C1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error
You do it this way:
=if(iserror(yourformula),0,yourformula) In Excel 2007, you can use the IFError function. Regards, Fred. "dadof4girls" wrote in message ... Is there any way to get #NUM! error to be hidden? The formula works great but for every instant returned that does not have a value iit shows this error. Formula used: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) then ctl shift enter Names in Column A Companies in Column B Name to look up C1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error
In XL03
=IF(ISERROR(your formula),"",your formula) =IF(ISERROR(INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$ C$1, ROW($A$1:$A$30)),ROW(A1)))),"", INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$1 :$A$30)),ROW(A1)))) In XL07 =IFERROR(INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1 , ROW($A$1:$A$30)),ROW(A1))),"") If this post helps click Yes --------------- Jacob Skaria "dadof4girls" wrote: Is there any way to get #NUM! error to be hidden? The formula works great but for every instant returned that does not have a value iit shows this error. Formula used: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) then ctl shift enter Names in Column A Companies in Column B Name to look up C1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error
Let's assume the first cell you enter the formula in is cell E1.
Array entered** : =IF(ROWS(E$1:E1)COUNTIF($A$1:$A$30,$C$1),"",INDEX ($B:$B,SMALL(IF($A$1:$A$30=$C$1,ROW($A$1:$A$30)),R OWS(E$1:E1)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks. -- Biff Microsoft Excel MVP "dadof4girls" wrote in message ... Is there any way to get #NUM! error to be hidden? The formula works great but for every instant returned that does not have a value iit shows this error. Formula used: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) then ctl shift enter Names in Column A Companies in Column B Name to look up C1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - error saving file & error loading dll | Excel Discussion (Misc queries) | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) |