Solved Problem With Nested If Statements

October 15, 2018 at 08:02:15
Specs: Windows 7
Hi, Can you help.

I'm looking to insert this formula in my spread sheet.

=IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6)," "))))

D3 = Period
Monthly or Quarterly or Annual or Bi-Annual

E3 is a date field

However, when there is no date in E3 I want the formula to give me blank.

Thanks in advance.


See More: Problem With Nested If Statements

Reply ↓  Report •

✔ Best Answer
October 18, 2018 at 11:50:59
Just in case you want to have some fun, this (shorter) formula seems to work also:

=IF(E3>0,EDATE(E3,CHOOSE(MATCH(CODE(D3),{65,66,77,81}),12,6,1,3)),"")

It would be a fun and educational exercise for you to figure out how it works. Give it a try and then come on back if you need any assistance with breaking it down.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
October 15, 2018 at 08:42:30
Not completely sure, but try this:

=IF(OR(D3="",E3=""),"",IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6),"")))))

First check to see if D3 or E3 is blank.

Is that what your looking for?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
October 15, 2018 at 08:42:34
Just add IF(E3="","",.....))))) as your first test.

An IF function stops at the first True result.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
October 15, 2018 at 12:22:44
BTW...In your formula, you are using a "space" to return a blank cell. A space is an actual text character and that could cause problems in certain cases.

It's better to use a null string, represented by "" (2 double quotes) to display an empty cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 18, 2018 at 06:44:46
Thanks for the suggestions. I've used the following code now;

=IF(E3="","",IF(D3="Monthly",EDATE(E3,1),IF(D3="Quarterly",EDATE(E3,3),IF(D3="Annual",EDATE(E3,12),IF(D3="Bi-Annual",EDATE(E3,6),"")))))


Reply ↓  Report •

#5
October 18, 2018 at 11:50:59
✔ Best Answer
Just in case you want to have some fun, this (shorter) formula seems to work also:

=IF(E3>0,EDATE(E3,CHOOSE(MATCH(CODE(D3),{65,66,77,81}),12,6,1,3)),"")

It would be a fun and educational exercise for you to figure out how it works. Give it a try and then come on back if you need any assistance with breaking it down.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question