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:
[TABLE="width: 289"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Start Date [/TD]
[TD]Milestone 1[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Testing[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1/30/2018[/TD]
[/TR]
[TR]
[TD]Monitoring[/TD]
[TD]1/1/2018[/TD]
[TD] [/TD]
[TD]1/30/2018[/TD]
[/TR]
[TR]
[TD]Testing[/TD]
[TD]1/1/2018[/TD]
[TD] [/TD]
[TD]1/30/2018[/TD]
[/TR]
[TR]
[TD]Monitoring[/TD]
[TD]1/1/2018[/TD]
[TD] [/TD]
[TD]1/30/2018[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[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
Type	Start Date	Milestone 1	End Date
Testing
Monitoring	N/A
Testing
Monitoring	N/A

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1/30/2018[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/1/2018[/TD]

[TD="align: right"]1/30/2018[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/30/2018[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/1/2018[/TD]

[TD="align: right"]1/30/2018[/TD]

</tbody>

Sheet20​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2="","",IF(A2="Testing",B3+3,"N/A"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"?