Solved

multiple if statement

Posted on 2014-02-17
14
154 Views
Last Modified: 2014-02-19
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
Comment
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
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 30

Expert Comment

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

Expert Comment

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

Author Comment

by:route217
ID: 39865116
Hi gowflow

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

Either...Formula would be better for learn...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:gowflow
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 30

Expert Comment

by:gowflow
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

by:route217
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

by:route217
ID: 39865146
Insert results into column E
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39865215
You are missing elses situations
gowflow
0
 

Author Comment

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

Sorry..
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 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			

Open in new window


check the file attached.
gowflow
Expiry.xlsx
0
 

Author Comment

by:route217
ID: 39865321
Much appreciated. .gowflow

Let me check the attached file.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39865342
ok let me know
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
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 30

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question