0

I have a formula that I am using in excel. The formula returns an array of values in the form of a column. I only know how to use an IF statement on a cell or a formula that returns one cell. However, I don't know how to apply it to get it to replace all the 0 values by NA() in the array returned by the formula. I want to wrap the formula using the IF statement.

It is a Reuters formula : =IF(RData(D17:D26;H16) = 0; NA(); RData(D17:D26;H16)), but it does not work at all.

RData(D17:D26;H16) returns the following column

H16 contains: AST_SWPSPD

D17:D26 contains the following RIC Codes: 

BMPS2YEUAM=R 
BMPS3YEUAM=R 
BMPS4YEUAM=R 
BMPS5YEUAM=R
BMPS7YEUAM=R 
BMPS10YEUAM=R 
BMPS20YEUAM=R 
BMPS30YEUAM=R

The resulting column is the following

201.7
499.5
389.2
470.6
306.8
0
0
525.3
525.3
525.3

I want to get rid of the zeros and replace them with NA Is that possible?

Thank you.

Helios
  • 151
  • 2
  • 11
  • 1
    Have you tried wrapping an IF around the formula? Also: What formula? And please add some sample data and desired results... – hsan Feb 20 '13 at 14:58
  • Sorry, I did not provide enough information. I tried wrapping in around the formula. It is a Reuters formula : =IF(RData(D17:D26;H16) = 0; NA(); RData(D17:D26;H16)), but it does not work at all. – Helios Feb 20 '13 at 15:43

3 Answers3

3

It should be exactly the same. Lets pretend your formula is F (but it would be much better if you added your actual formula or something similar to your question)

=IF(F=0, NA(), F)

Will work even if F is returning an array. Just replace F with your entire formula and don't forget to press ctrl+shift+enter

Dan
  • 45,079
  • 17
  • 88
  • 157
  • Sorry, I did not provide enough information. I tried wrapping in around the formula. It is a Reuters formula : =IF(RData(D17:D26;H16) = 0; NA(); RData(D17:D26;H16)), but it does not work at all. – Helios Feb 20 '13 at 15:43
  • @Helios Judging by the edit on your question Dan's answer should work. Try testing it with something like `{=IF({1,2,3,4,5}=3,NA(),{1,2,3,4,5})}` which will replace the `3` with `#N/A`. – hsan Feb 20 '13 at 16:39
  • It actually works that way as well for me but not with the formula. There is a way to go around it though. – Helios Feb 20 '13 at 17:02
  • Why do you use ; instead of ,? Is that a locale thing? – Dan Feb 21 '13 at 06:41
1

Thank you all for your input,

I found a way to go around it, simply by returning one value at a time and wrapping it with the IF statement.

So it becomes: =IF(RData(D17;$H$16)=0;NA(); RData(D17;$H$16))

I am fixing H16 because it contains the argument needed for the formula.

This way it works.

Helios
  • 151
  • 2
  • 11
  • If you managed to solve your own problem then you should mark this as the correct answer so people know it's solved – Dan Feb 21 '13 at 07:35
  • actually, this is only a way to go around it, not really THE solution because I still do not understand how to apply the formula to a range and wrap it to return what I want. – Helios Feb 21 '13 at 15:04
1

Instead of using =IF(RData(D17;$H$16)=0;NA(); RData(D17;$H$16)) you can also use 

=IFERROR(1/(1/(RData(D17;$H$16)));NA())

This way, your function gets only called once, which will reduce calculation time.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88