Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# multiple if statement

Posted on 2014-02-17
Medium Priority
157 Views
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
Question by:route217
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 5

LVL 31

Expert Comment

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

LVL 31

Expert Comment

ID: 39865089
you want this via formula or VBA ?
gowflow
0

Author Comment

ID: 39865116
Hi gowflow

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

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

LVL 31

Expert Comment

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

LVL 31

Expert Comment

ID: 39865125
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 Comment

ID: 39865138
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 Comment

ID: 39865146
Insert results into column E
0

LVL 31

Expert Comment

ID: 39865215
You are missing elses situations
gowflow
0

Author Comment

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

Sorry..
0

LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 39865255
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 Comment

ID: 39865321
Much appreciated. .gowflow

Let me check the attached file.
0

LVL 31

Expert Comment

ID: 39865342
ok let me know
gowflow
0

LVL 31

Expert Comment

ID: 39865447
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

LVL 31

Expert Comment

ID: 39866956
Did you try it out any news?
gowflow
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month7 days, 17 hours left to enroll