IF cell is 0-2 years then 100% IF 2-3 years then 75% and so on....

[Padthelad]

Hi,

I am having trouble working the logic for the below scenario:-

I am aiming to achieve a workbook that will automatically tell me what deduction to make if time between two dates is under 2 years (100%), 2-3 years (75%), 3-4 years (50%) and over 4 years (0%).

I have a workbook with a list of dates in column B and column G. Column H has the difference in years, months and days. Column H has the following code:-

=DATEDIF(B7,G7,"y")&" years "&DATEDIF(B7,G7,"ym")&" months "&DATEDIF(B7,G7,"md")&" days"

I am trying to have column I auto populate with the correct deduction depending on the difference between the two dates in column B and G. Table is as follows:-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]02/01/2018
[/TD]
[TD]ITEM 1
[/TD]
[TD]105.00
[/TD]
[TD]21.00
[/TD]
[TD]126.0
[/TD]
[TD]14/09/2018
[/TD]
[TD]0 years 8 months 12 days
[/TD]
[TD]100%
[/TD]
[/TR]
</tbody>[/TABLE]

The formula I currently have in I1 is:-

=IF(DATEDIF(B6,TODAY(),"y")<=2,"100%",$T$10)

This gives me the desired result, but it does not change if the dates are changed in column B and G.

I have the following table where the deduction amounts are stored. Ideally I would like the formula to take it's information from here so that changes to deductions can be made in the future.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Up to 2 Years
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]100%
[/TD]
[TD]No Deduction
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]2-3 years
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]75%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]3-4 years
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Over 4 years
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this all makes sense. I think I may be using the wrong formulas for this problem.

Any help or advice is very much appreciated.

Thanks,

Pad

 

[Small Paul]

Hi Pad
I "think" I have a solution for you!
Using IF statements:

=IF(I1<R$1,S$1,IF(I1><r$1,s$1,if(i4>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<R$2,S$2))))[CODE]<r$2,s$2))))[ code]

where:
col B is the start date
cols C to F have been simply copied from your table (sum?)
col G is today's's date
Col H is =G1-B1 (gives the number of days)
Col I is =H1/365 (gives the years a a decimal)
Col J is the formula above
Cell Q1 is blank, Q2 is 2, Q3 is 3 and Q4 is 4
Cell R1 is 2, R2 is 3, R3 is 4 and R4 is blank
Cell S1 is 100%, S2 is 75%, S3 is 50% and S4 is 0%
Cell T1 is 0%, T2 is 25%, T3 is 75% and T4 is 100%

Changing the values in Columns Q to T can be amended and the result will change.
The order of the IF statements is key.

Hope I have understood your issue correctly!
Small Paul.</r$2,s$2))))[></r$1,s$1,if(i4>

 

[Small Paul]

=IF(I1<R$1,S$1,IF(I1>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<R$2,S$2))))

It seems only part of the statement copied

 

[Small Paul]

=if(i1<r$1,s$1,if(i1>q$4,s$4,if(i1>q$3,s$3,if((i1<r$2,s$2))))

 

[dreid1011]

Another option:

=LOOKUP(DATEDIF(B6,TODAY(),"y"),{0,2,3,4,...},{"100%","75%","50%","25%",...})

Note the highlighted ... are not part of the formula. I added them to indicate if you had more ranges.

 

[Joe4]

Small Paul,

If you are using > or < in your formulas, surround them with spaces on either side. Otherwise, they get treated as HTML tags, and your formulas get cut off like that.

 

[Small Paul]

=IF(I1 < R$1,S$1,IF(I1 > Q$4,S$4,IF(I1 > Q$3,S$3,IF(I1 < R$2,S$2))))

Thank you Joe4

 

[Padthelad]

Hi Pad
I "think" I have a solution for you!
Using IF statements:

=IF(I1<r$1,s$1,if(i1><r$1,s$1,if(i4>Q$4,S$4,IF(I1>Q$3,S$3,IF(I1<r$2,s$2))))[code]<r$2,s$2))))[ code]

where:
col B is the start date
cols C to F have been simply copied from your table (sum?)
col G is today's's date
Col H is =G1-B1 (gives the number of days)
Col I is =H1/365 (gives the years a a decimal)
Col J is the formula above
Cell Q1 is blank, Q2 is 2, Q3 is 3 and Q4 is 4
Cell R1 is 2, R2 is 3, R3 is 4 and R4 is blank
Cell S1 is 100%, S2 is 75%, S3 is 50% and S4 is 0%
Cell T1 is 0%, T2 is 25%, T3 is 75% and T4 is 100%

Changing the values in Columns Q to T can be amended and the result will change.
The order of the IF statements is key.

Hope I have understood your issue correctly!
Small Paul.</r$2,s$2))))[code]<r$2,s$2))))[></r$1,s$1,if(i4>[/QUOTE]

Thanks Small Paul.

You method worked, but the method below is easier for my team to understand.

Thank you!
</r$1,s$1,if(i1>

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I	J
6	2-Jan-18					14-Sep-18		100%
7	2-Jan-18					14-Sep-18		100%	100%
8	2-Jan-16					14-Sep-18		75%	75%
9
10
11	14-Sep-16					14-Sep-18			100%
12	13-Sep-16					14-Sep-18			75%
13	2-Jan-15					14-Sep-18			50%
14	02-01-14					14-Sep-18			0%
15
7c

Cell Formulas
Range	Formula
I6	=IF(DATEDIF(B6,TODAY(),"y")<=2,1,$S$9)
I7	=LOOKUP(DATEDIF(B7,TODAY(),"y"),{0,1;2,0.75;3,0.5;4,0})
J7	=IF((G7-B7)<731,1,IF((G7-B7)<1096,0.75,IF((G7-B7)<1461,0.5,0)))
J11	=IF((G11-B11)<731,1,IF((G11-B11)<1096,0.75,IF((G11-B11)<1461,0.5,0)))

 

[Padthelad]

Another option:

=LOOKUP(DATEDIF(B6,TODAY(),"y"),{0,2,3,4,...},{"100%","75%","50%","25%",...})

Note the highlighted ... are not part of the formula. I added them to indicate if you had more ranges.

Thanks Dreid, this worked a treat!