Formula for Slab based conditions

[pavanranjan]

Hi Guys,

Need your help to build a formula for below slab-table wherein comparing values from Slab range ,Penalty % and Slab base.

Condition is Slab range must achieve slab base to get lower penalty

	Decrease
Slab Base	Slab Range	Penalty %
Below 30	0-30	0.0%
30	30.01-44.99	1.0%
45	45.01-59.99	2.0%
60	60.01-74.99	3.0%
75	75.01-89.99	4.0%
90	90.01-104.99	5.0%
105	105.01-119.99	6.0%
120	120.01-134.99	7.0%
135	135.01-149.99	8.0%
150	150.01-164.99	9.0%
165	165.01-194.99	10.0%

Meet Google Drive – One place for all your files

 

[Dave Patton]

What is the penalty at say 100?
Can you post your data with XL2BB?

 

[pavanranjan]

What is the penalty at say 100?
Can you post your data with XL2BB?

testing.xlsx
	E	F	G
12		Decrease
13	Slab Base	Slab Range	Penalty %
14	Below 30	0-30	0.0%
15	30	30.01-44.99	1.0%
16	45	45.01-59.99	2.0%
17	60	60.01-74.99	3.0%
18	75	75.01-89.99	4.0%
19	90	90.01-104.99	5.0%
20	105	105.01-119.99	6.0%
21	120	120.01-134.99	7.0%
22	135	135.01-149.99	8.0%
23	150	150.01-164.99	9.0%
24	165	165.01-194.99	10.0%
Sheet2

Cell Formulas
Range	Formula
E16:E24	E16	=+E15+15
G16:G24	G16	=+G15+1%

Penalty for 100 would be 5%

 

[Dave Patton]

N.B. E15 etc are 30.00001
What result do you require for 45 even?

T202011a.xlsm
	E	F
9	100	5.0%
10	30	0.0%
11	30.5	1.0%
12
13	Slab Base	Penalty %
14	0	0%
15	30	1%
16	45	2%
17	60	3%
18	75	4%
19	90	5%
20	105	6%
21	120	7%
22	135	8%
23	150	9%
24	165	10%
25
5a

Cell Formulas
Range	Formula
F9:F11	F9	=LOOKUP(E9,$E$14:$F$24)
F16:F24	F16	=+F15+1%

 

[Dave Patton]

Why is 105 6% If that is correct, consider the following.

T202011a.xlsm
	E	F
8
9	100	5.0%
10	105	6.0%
11	45	2.0%
12
13	Slab Base	Penalty %
14	0	0%
15	30	1%
16	45	2%
17	60	3%
18	75	4%
19	90	5%
20	105	6%
21	120	7%
22	135	8%
23	150	9%
24	165	10%
25
5a

Cell Formulas
Range	Formula
F9:F11	F9	=LOOKUP(E9,$E$14:$F$24)

​

 

[Dave Patton]

T202011a.xlsm
	E	F	G
9	30	0.0%	0.0%
10	45	2.0%	2.0%
11	165	10.0%	10.0%
12
13	Slab Base	Penalty %
14	0	0%
15	30.0001	1%
16	45	2%
17	60	3%
18	75	4%
19	90	5%
20	105	6%
21	120	7%
22	135	8%
23	150	9%
24	165	10%
5a

Cell Formulas
Range	Formula
F9:F11	F9	=LOOKUP(E9,$E$14:$F$24)
G9:G11	G9	=(E9>30)*(INT(E9/15)-1)/100

Definition is inconsistent.
N.B. E15 is 30.0001

 

[pavanranjan]

Hi Dave,

for more clarity ,please note the details as below..

There should be a logic of base slab in formula,if the value doesn't hit the same he will be falling in upper slab.Hope i am able to explain you.

few examples given below.Slab will always be in 2 decimal points only.

Slab	output	Remarks
30	0%
30.01	1%
62	4%	because lower slab base not got hit hence will be falling under upper slab(till he achives 60)
76	5%	because lower slab base not got hit hence will be falling under upper slab(till he achives 75)

 

[Dave Patton]

Did you try the suggestions? Post #6 gives 2 alternatives.
We require clearer information.

The suggestions follow the tables in your initial post.
The tables do not show the result what the amount meets the threshold see 45, 60, ...
62 appears to be 3% per your tables
76 appears to be 4% per your tables