Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

multiple if statement

Posted on 2014-02-17
14
Medium Priority
?
158 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
  • 9
  • 5
14 Comments
 
LVL 31

Expert Comment

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

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
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!

 
LVL 31

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 31

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 31

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 31

Accepted Solution

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

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 31

Expert Comment

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

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 31

Expert Comment

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

926 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