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

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
Asked:
route217
  • 9
  • 5
1 Solution
 
gowflowCommented:
could you post a sample workbook with data ?
gowflow
0
 
gowflowCommented:
you want this via formula or VBA ?
gowflow
0
 
route217Author Commented:
Hi gowflow

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

Either...Formula would be better for learn...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
gowflowCommented:
ok when you say today you mean to refer to cell K1 right ? or whatever the value in that cell ?
gowflow
0
 
gowflowCommented:
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
 
route217Author 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
 
route217Author Commented:
Insert results into column E
0
 
gowflowCommented:
You are missing elses situations
gowflow
0
 
route217Author Commented:
That's true..had to get. Quick mock up..the firewall is strict. .

Sorry..
0
 
gowflowCommented:
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
 
route217Author Commented:
Much appreciated. .gowflow

Let me check the attached file.
0
 
gowflowCommented:
ok let me know
gowflow
0
 
gowflowCommented:
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
 
gowflowCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now