Target, Minimum, and Maximum formula Automation

[Lesliebryce]

Hi,
I need to calculate incentive and it’s based on the minimum, Target, and maximum.
minimum of 50 files gets an incentive of 90%
target of 60 files gets an incentive of 100%
Maximum of 90 files gets an incentive of 150%.
So if someone has 70 files they fall between target and maximum, which would be over the 100% but less than the 150% and the difference of percentage needs be calculated so for example that individual would be at 116% payout. How can I calculate that to incorporate it?

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

Book1.xlsm
	D	E	F	G
1	Values	Percent
2	45	75.00%	Less Than Minimum
3	50	83.33%	Less Than Minimum
4	60	100.00%	Target
5	70	116.67%	Between Target And Maxiumum
6	75	125.00%	Between Target And Maxiumum
7	80	133.33%	Between Target And Maxiumum
8	90	150.00%	Maximum
9	100	166.67%	Over the Maximum
10	105	175.00%	Over the Maximum
11	120	200.00%	Over the Maximum
12	125	208.33%	Over the Maximum
13	135	225.00%	Over the Maximum
14	140	233.33%	Over the Maximum
15	150	250.00%	Over the Maximum
16	160	266.67%	Over the Maximum
17	175	291.67%	Over the Maximum
18
Sheet1

Cell Formulas
Range	Formula
E2:E17	E2	=D2/60
F2:F17	F2	=IF(E2<0.9,"Less Than Minimum",IF(E2=0.9,"Minimum",IF(E2<1,"Between Minimum And Target",IF(E2=1,"Target",IF(E2<1.5,"Between Target And Maxiumum",IF(E2=1.5,"Maximum","Over the Maximum"))))))

 

[maabadi]

Or Other Method at Column D

Book1.xlsm
	A	B	C	D	E
1	Values	Percent	Method 1	Method 2
2	45	75.00%	Less Than Minimum	Less Than Minimum
3	50	83.33%	Less Than Minimum	Less Than Minimum
4	60	100.00%	Target	Target
5	70	116.67%	Between Target And Maxiumum	Between Target And Maximum
6	75	125.00%	Between Target And Maxiumum	Between Target And Maximum
7	80	133.33%	Between Target And Maxiumum	Between Target And Maximum
8	90	150.00%	Maximum	Maximum
9	100	166.67%	Over the Maximum	Over The Maximum
10	105	175.00%	Over the Maximum	Over The Maximum
11	120	200.00%	Over the Maximum	Over The Maximum
12	125	208.33%	Over the Maximum	Over The Maximum
13	135	225.00%	Over the Maximum	Over The Maximum
14	140	233.33%	Over the Maximum	Over The Maximum
15	150	250.00%	Over the Maximum	Over The Maximum
16	160	266.67%	Over the Maximum	Over The Maximum
17	175	291.67%	Over the Maximum	Over The Maximum
18
Sheet1

Cell Formulas
Range	Formula
B2:B17	B2	=A2/60
C2:C17	C2	=IF(B2<0.9,"Less Than Minimum",IF(B2=0.9,"Minimum",IF(B2<1,"Between Minimum And Target",IF(B2=1,"Target",IF(B2<1.5,"Between Target And Maxiumum",IF(B2=1.5,"Maximum","Over the Maximum"))))))
D2:D17	D2	=CHOOSE((B2>0) + (B2=0.9) + (B2>0.9)*2 + (B2=1)+ (B2>1)*2 + (B2=1.5) + (B2>1.5)*2, "Less Than Minimum", "Minimum", "Between Minimum And Target", "Target", "Between Target And Maximum","Maximum", "Over The Maximum")