Formual Help Please

[rstagman]

Good afternoon! I'm working on a formula for Column C to add days to the start date based on the type. Below is the formula I'm trying to use. It works perfectly when the type = "Testing", however, I'm not getting a "N/A" result when the type equals "Monitoring". Can someone take a look at this for me and let me know where I'm messing up?

FORMULA USED:
=IF(B2="","",IF(A2="Testing",B3+3,"N/A"))



DATA:

Type	Start Date	Milestone 1	End Date
Testing			1/30/2018
Monitoring	1/1/2018		1/30/2018
Testing	1/1/2018		1/30/2018
Monitoring	1/1/2018		1/30/2018

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[Joe4]

It works fine for me.
Are you sure that you have automatic calculations turned on?
What happens if you press F9?

 

[jproffer]

This is another way to try it, but what you have looks right, EXCEPT.......you have B3+3. Are you really meaning to step it down one row? If so it should work, but anyhow...another way:

=IF(AND(B2<>"",A2="Testing"),B3+3,"N/A")

But like I said, the 2 formulas are basically the same, and I can't see anything wrong with what you had with the exception of the stepdown in row, as mentioned.

 

[jtakw]

Hi,

Yes, as Joe4 said, there's nothing wrong with your formula and should work, only 1 thing I'd like to point out, If A2="Testing", you're adding B3 to 3, and Not B2, may be that's on purpose, pointing it out just in case.

Excel 2010

	A	B	C	D
1	Type	Start Date	Milestone 1	End Date
2	Testing			1/30/2018
3	Monitoring	1/1/2018	N/A	1/30/2018
4	Testing	1/1/2018	1/4/2018	1/30/2018
5	Monitoring	1/1/2018	N/A	1/30/2018

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

Worksheet Formulas Cell Formula C2 =IF(B2="","",IF(A2="Testing",B3+3,"N/A")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[rstagman]

The calculation piece is working for me. I'm just not getting a return of N/A when the cell equals something other than Testing.

 

[rstagman]

I apologize. The formula should read as follows:

=IF(B2="","",IF(A2="Testing",B2+3,"N/A"))

 

[rstagman]

=IF(AND(B2<>"",A2="Testing"),B3+3,"N/A")

This formula returns the "N/A" I was looking for. The one I created is not returning it for some reason. I tried pressing f9 and nothing.

 

[Joe4]

As jrpoffer and jtakw pointed out, I suspect you are messing up your range references, as you appeared to have done it again:

[COLOR=#333333]=IF(AND([/COLOR][COLOR=#ff0000][B]B2[/B][/COLOR][COLOR=#333333]<>"",A2="Testing"),[/COLOR][B][COLOR=#ff0000]B3[/COLOR][/B][COLOR=#333333]+3,"N/A")[/COLOR]

 

[jproffer]

The only reason your original formula would not return "N/A" when A2<>"Testing" is if B2 was empty, then it would return nothing.

Out of curiosity, what WERE you getting when A2 was something other than "Testing"?