• Status: Solved
• Priority: Medium
• Security: Public
• Views: 165

# multiple if statement

Hi Experts excel 2007

There are 4 data items that are involved in the determination of New Expiry Date

O/D Expiry Date
BDCS Ref
Secured (y/n)?
Total Debt

The decision tree looks like this;

IF O/D Expiry Date > 2 months from today

THEN New Expiry Date = O/D Expiry Date

ELSE

IF BDCS Ref = F, G, H, I or J

THEN New Expiry Date = today plus 2 months

ELSE

IF Secured (y/n)? = “Y” AND Total Debt < £500k

THEN New Expiry Date = today plus 5 months

ELSE

IF Secured (y/n)? = “Y” AND Total Debt >= £500k

THEN New Expiry Date = today plus 2 months

ELSE

IF Secured (y/n)? = “N” AND Total Debt < £250k

THEN New Expiry Date = today plus 5 months

ELSE New Expiry Date = today plus 2 months

Col A.
O/D Expiry Date

Col B
EDCF Ref

Col c
Secured

Col D
Total Debt

Col K 1 = todays date
0
route217
• 9
• 5
1 Solution

Commented:
could you post a sample workbook with data ?
gowflow
0

Commented:
you want this via formula or VBA ?
gowflow
0

Author Commented:
Hi gowflow

Sorry cannot up load files from my location. ..sincere apologies. .

Either...Formula would be better for learn...
0

Commented:
ok when you say today you mean to refer to cell K1 right ? or whatever the value in that cell ?
gowflow
0

Commented:
Oops confusing.

Col A is O/D Expiry Date
K1 = today's date (what does it have to do here ???)
New Expiry Date where do we put this ??

gowflow
0

Author Commented:
Hi gowflow

K1 has the formula =text (now (),"dd/mm/yy")... which is the current date.

See attached file...can delete file afterwards even if its made up data?
Book1.xlsx
0

Author Commented:
Insert results into column E
0

Commented:
You are missing elses situations
gowflow
0

Author Commented:
That's true..had to get. Quick mock up..the firewall is strict. .

Sorry..
0

Commented:
here is how if then else are supposed to be please confirm all the ifs are correct and nothing is missing as I tried some sample data and especially the one you posted you did not give me the result so not sure if the formula is correct.

``````IF O/D Expiry Date > 2 months from today THEN
THEN New Expiry Date = O/D Expiry Date
ELSE
IF BDCS Ref = F, G, H, I or J THEN
THEN New Expiry Date = today plus 2 months
ELSE
IF Secured (y/n)? = “Y” AND Total Debt < £500k THEN
THEN New Expiry Date = today plus 5 months
ELSE
IF Secured (y/n)? = “Y” AND Total Debt >= £500k THEN
THEN New Expiry Date = today plus 2 months
ELSE
IF Secured (y/n)? = “N” AND Total Debt < £250k THEN
THEN New Expiry Date = today plus 5 months
ELSE New Expiry Date = today plus 2 months
``````

check the file attached.
gowflow
Expiry.xlsx
0

Author Commented:
Much appreciated. .gowflow

Let me check the attached file.
0

Commented:
ok let me know
gowflow
0

Commented:
the best for you is to have put the result near the data you uploaded so we can make sure the formula is correct. Nevertheless, if you feel the formula is not correct please put the result in an other column of what it should be and review your logic of events so we can finetune this.
gowflow
0

Commented:
Did you try it out any news?
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.