| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can i change the #N/A error for a 0 value
I have a list of people......
a1:a3 b1:b3 7 Ana 8 Marie 9 Susan I have the following formula: =index(a1:b3,match("susan",b1:b3,0),1) The result is: 9 But if the list does not have Susan, it gave me #N/A error value.... i need to add the result in a formula...... but with the error value it is not possible. is there any way to get 0 instead of #N/A when the formula does not mach the list? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can i change the #N/A error for a 0 value
=if(isna(match("susan",b1:b3,0)),0,index(a1:a3,mat ch("susan",b1:b3,0)))
(I changed your =index() a bit.) And if you're using xl2007, look at =iferror() in excel's help. Teresita wrote: I have a list of people...... a1:a3 b1:b3 7 Ana 8 Marie 9 Susan I have the following formula: =index(a1:b3,match("susan",b1:b3,0),1) The result is: 9 But if the list does not have Susan, it gave me #N/A error value.... i need to add the result in a formula...... but with the error value it is not possible. is there any way to get 0 instead of #N/A when the formula does not mach the list? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can i change the #N/A error for a 0 value
Try the following IF variation:
=IF(ISNA(INDEX(A1:B13,MATCH("susan",B1:B3,0),1)),0 ,INDEX(A1:B13,MATCH("susan",B1:B3,0),1)) -- Kevin Backmann "Teresita" wrote: I have a list of people...... a1:a3 b1:b3 7 Ana 8 Marie 9 Susan I have the following formula: =index(a1:b3,match("susan",b1:b3,0),1) The result is: 9 But if the list does not have Susan, it gave me #N/A error value.... i need to add the result in a formula...... but with the error value it is not possible. is there any way to get 0 instead of #N/A when the formula does not mach the list? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I change an #N/A error to display other text? | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| How to change and error message to text | Excel Worksheet Functions | |||
| Tab Name Change Error .xls] inserted | Excel Discussion (Misc queries) | |||
Linear Mode
