How can I turn #N/A N/A into "0"?

[TwoAce]

Dear all,

The following function is returning #N/A N/A: =BDP($D22,$BD$19)
This is because the feature (marketable securities=$BD$19) is not available for this security ($D22).
I would like this formula to be changed so it will return "0" (zero).
ISNA will get rid of a single #N/A for me, but I can't get it to do this for #N/A N/A.
I hope someone has a solution.

Thanks in advance!

TwoAce

 

[onlyadrafter]

Hello,

does

=IF(iserror(formula),0,formula)

work?

 

[TwoAce]

=IF(ISERROR(BDP($D22,$BD$19)),0,BDP($D22,$BD$19))

This is not working, it's still returning #N/A N/A unfortunately.

 

[Jonmo1]

I think we're going to need to see the code for the BDP function...

It's possible it's not actually an NA Error, maybe the resulting value from the BDP formula is actually #N/A N/A

 

[TwoAce]

That could actually be the case, although I'm not quite sure. It's a code for Bloomberg, which can be used when the Bloomberg addin is installed.
I could find another way around it maybe, by adding an extra column that says if the cell in the previous column >0 then, take this value, if not, 0.
I have to recalculate from this value, and use the recalculated value again... but I think it might work like I described above. It just gets ugly with these extra columns (I need to apply it to other cells as well).

Thanks for the help, I'll see if it works the way I suggested. If anyone has other ideas, I'd like to hear it as well.

 

[Jonmo1]

maybe the resulting value from the BDP formula is actually #N/A N/A

If that's the case, maybe this will work

=IF(BDP(...)="#N/A N/A",0,BDP(...))

 

[Aladin Akyurek]

Dear all,

The following function is returning #N/A N/A: =BDP($D22,$BD$19)
This is because the feature (marketable securities=$BD$19) is not available for this security ($D22).
I would like this formula to be changed so it will return "0" (zero).
ISNA will get rid of a single #N/A for me, but I can't get it to do this for #N/A N/A.
I hope someone has a solution.

Thanks in advance!

TwoAce

If BDP is supposed to return a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,BDP($D22,$BD$19)))

 

[TwoAce]

Aladin, as many times before you come up with the perfect solution. I could not have thought of it myself. Thanks a lot, you've made my day!

I've tried Jonmo1's solution as well, and it also works! I'll see which one I choose

 

[Makrini]

Your topic reminded me of a funny thing that happened at work.

We run an answering service where we fill out a form and send a message.
Our agent asked the caller a series of questions - all of which were not applicable. The agent put NA in all the fields.

Pager owner rang and asked why we sent him a message that said " na na na na naaa!"

rofl - sorry to hijack your thread