Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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