# excel error msg "#N/A N/A" replacement



## ricuelo (Jan 10, 2009)

hi,
is there anyway to replace the text "#N/A N/A" when you get an error msg with a 0 or better yet a dash " - " in an excel spreadsheet. i've tried writing IF statments but am not very good at them and haven't had any luck. the best i've been able to do is use conditional formatting to change font to white and hide the error msg. the problem with that is that when i copy and paste the info into other programs the "#N/A N/A" appears.

pls help

this is is the formula that is pulling the information which sometimes populates the error msg. please use it as a sample to write an IF statement or whatever other solution is possible. thx 
=BDP($A12&"@"&Q12&" corp",L$6)


----------



## xstext (Mar 19, 2009)

I'll leave it to you to sort out the Bloomberg function's role within this (because while I'm aware of it, I've never actually utilized it), but ultimately the structure is as follows:

=IF(ISERROR(A1/B1)," - ",(A1/B1))

Basically: 
Conditional (IF)
Logical Test (ISERROR)
The formula that is being tested by the conditional (A1/B1)
The result that should be returned by the logical test if the formula throws an error (" - ")
Otherwise calculate the formula as it is and return it's value (A1/B1)

Apply that logic to your BDP function.
(and yes... you can alter the " - " to whatever result you wish it to display)


----------



## Zack Barresse (Jul 25, 2004)

I would hazard against returning text, especially " - ". 
What does the BDP() return? If it's literally returning "#N/A N/A", then it's text, not an actual Excel formula-generated error, so the ISERROR() return would probably not be the best. You can do an IF() function, or if it's supposed to return a number, just use the ISNUMBER() function. Depending on what you are returning with BDP(), you may want to either use 0 or null ("").

HTH


----------

