Apply Slab Rates????

**ndmalam** · 05-09-2007, 10:21 PM

Hi,
I want to apply slab rates to a figure.
For Example I have got a Number 400
Now want to apply slab rates like this

First 100 at 4.5/=
Next 200 hundreds (from 101 to 300) at 5/=
More than 300 at 6/=
please help creating formula
best regards

**Dav** · 05-10-2007, 03:08 AM

well it is not clear how you apply the rates but if it is only 3 levels, something like the following if the quantity is is a1

if(a1<=100,4.5,if(a1<=300,5,6))

and modifiy as appropriate. If you gave more information you would get a better answer

otherwise if there are more bands, looking at a vlookup table is more straightforward

Regards

Dav

**starguy** · 05-10-2007, 03:13 AM

Originally Posted by ndmalam

Hi,
I want to apply slab rates to a figure.
For Example I have got a Number 400
Now want to apply slab rates like this

First 100 at 4.5/=
Next 200 hundreds (from 101 to 300) at 5/=
More than 300 at 6/=
please help creating formula
best regards

try this

=IF(A1<=100,A1*4.5,IF(AND(A1>100,A1<=300),450+(A1-100)*5,IF(A1>300,1450+(A1-300)*6)))

**Dav** · 05-10-2007, 03:22 AM

Sorry I misread, but as the ifs are nested starguys formula simplifies to

=IF(A1<=100,A1*4.5,IF(A1<=300,450+(A1-100)*5,1450+(A1-300)*6))

Regards

Dav

**ndmalam** · 05-10-2007, 09:59 PM

Sorry ,
I think I could not explain it properly.
This is an electricity Bill
totoal unit consumed are 400
Firtst 100 wil be charged at $2/=
From 101 to 300 at $3/=
All above 300 at $5/=
all three result in serpeate cells required
Sum of these three will be shown in Fourth cell

Pleae help
Best regards

**starguy** · 05-15-2007, 06:41 AM

Originally Posted by ndmalam

Sorry ,
I think I could not explain it properly.
This is an electricity Bill
totoal unit consumed are 400
Firtst 100 wil be charged at $2/=
From 101 to 300 at $3/=
All above 300 at $5/=
all three result in serpeate cells required
Sum of these three will be shown in Fourth cell

Pleae help
Best regards

Hi
I suppose you enter units consumed in A1

For first 100 at $2
=IF(A1="","",IF(A1<=100,A1*2,200))

For 101 to 300 at $3
=IF(A1<=100,"",IF(AND(A1>100,A1<=300),(A1-100)*3,600))

For above 300 at $5
=IF(A1>300,(A1-300)*5,"")

**daddylonglegs** · 05-15-2007, 09:33 AM

You could calculate the 3 amounts with these formulas

=MIN(A1,100)*2
=MEDIAN(0,200,A1-100)*3

and

=MAX(0,A1-300)*5

obviously sum these for the total...or for total with a single formula....

=A1*5-MIN(A1,300)*2-MIN(100,A1)

**ndmalam** · 05-15-2007, 10:21 PM

Thnaks Guys
It is ok ,
Both answers are good and working.
Best Regards

**hccbplp** · 12-24-2018, 11:46 AM

Can anyone help me out for the working below mention slabs.

Slab Range Rupees
Slab 1 40 to 59 0.50
Slab 2 61 to 79 0.75
Slab 3 81 to 114 1.00
Slab 4 116 to 164 1.25
Slab 5 60 1.00
Slab 6 80 1.5
Slab 7 115 2
Slab 8 165 2.5

**shg** · 12-24-2018, 04:13 PM

	A	B	C	D
1	Qty	Rate	Delta
2	0	0.00	0.00	C2: =B2-N(B1)
3	40	0.50	0.50
4	60	1.00	0.50
5	61	0.75	-0.25
6	80	1.50	0.75
7	81	1.00	-0.50
8	116	1.25	0.25
9	165	2.50	1.25
10
11	Qty	Fee
12	39	0.00		B12: =SUMPRODUCT((A12 > $A$2:$A$9) * (A12 - $A$2:$A$9) * $C$2:$C$9)
13	40	0.00
14	59	9.50
15	60	10.00
16	61	11.00
17	79	24.50
18	80	25.25
19	81	26.75
20	116	61.75
21	164	121.75
22	165	123.00
23	166	125.50
24	200	210.50

**FDibbins** · 12-25-2018, 01:09 AM

hccbplp welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.