Solved

multiple if statement

Posted on 2014-02-17
14
150 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 29

Expert Comment

by:gowflow
Comment Utility
could you post a sample workbook with data ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
you want this via formula or VBA ?
gowflow
0
 

Author Comment

by:route217
Comment Utility
Hi gowflow

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

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

Expert Comment

by:gowflow
Comment Utility
ok when you say today you mean to refer to cell K1 right ? or whatever the value in that cell ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
Insert results into column E
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You are missing elses situations
gowflow
0
 

Author Comment

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

Sorry..
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
Much appreciated. .gowflow

Let me check the attached file.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok let me know
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
Did you try it out any news?
gowflow
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now