Please Help: Need my 0's to turn into "N/A" if after cutoff date

**Clueless_Intern** · 08-13-2012, 09:42 AM

Hi everyone,

I have 19 daily investment returns for 12 different investment projects. On some days I get a positive return and on many others I simply have zero. I list the 12 investments in Column A and 19 days in Row 1.

For each investment project I also have a cuttoff date, listed under Column B, after which I no longer care how it performs. Many projects share the same cutoff date.

For the returns which come after the cutoff date listed in Column B, I would like it to show N/A. (As far as I can tell, this is essentially turning a bunch of "0" into "N/A" but my real data is pretty big so I'm not positive if this is the case. It's possible that I have some non-zero values in there.)

Can anyone tell me what's the best way to do this? Is it conditional formating?

I've attached a sample worksheet to give you an idea. Thanks.

**Dave H9** · 08-13-2012, 09:47 AM

In your daily returns try the following formula -

=IF(SUM(D2:D13)=0,"N/A",(SUM(D2:D13)))

Obviously this is for column D, just copy along for the full range of dates

Dave H

**Clueless_Intern** · 08-13-2012, 10:01 AM

Hi Dave H, thanks for responding. But sorry, I'm not sure what cell I should put the above formula in.

I also have a feeling that I did not explain my problem properly. I need each cell to actually show "N/A" if it's after a cutoff date.

For example, for Investment 1, after the date of 1/5/1994, all the returns from 1/6/1994 to 1/19/1994 should say "N/A." I've done this by hand in my updated sample worksheet and highlighted it in blue.

In reality, I need to do this for about 3 years worth of data, so I'm wondering if there's a quick way. Thanks.

**Dave H9** · 08-14-2012, 10:07 AM

I can't see a simple way of doing this without using code. My simplest solution would be as attached, you still put the data in as before but I have created a seperate worksheet you can use to view the data which will display the N/A after the cut off date. It is a bit od a time consuming way of doing things as you have to adjust each formula in each populated row of column D before you will be able to copy across the rest of the sheet. See what you think.

Dave H