Sliding scale pay charge formula

[sinners1978]

Hi, Hope someone can help.

I a looking for formula to help calculate charges to a client based on a sliding scale.

EG

For =5 hours, charge £30.00ph
For 6-10 hours, charge £29.50ph
For 11-15 hours, charge £29.00ph
or 16-20 hours, charge £28.50ph
For 21 + Hours, charge £28.00ph

So, for a client needing 5 hours the charge should be £30*5
For a client needing 18 hours the charge should be (£30*5 + £29.50*5 £29.00*5 + £28.5*3)
TIA

 

[lrobbo314]

Let's say you have this lookup table in cells A1:B6...

Hours	Rate
0	30
6	29.5
11	29
16	28.5
21	28

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Then You have the lookup value of 18 in cell D1. Then in E1, you can put this formula to get your total.

=SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&D1)),$A$2:$A$6,$B$2:$B$6))

 

[shg]

	A​	B​	C​	D​
1​	Hrs​	Rate​	Delta​	​
2​	0​	$ 30.00​	$ 30.00​	C2: =B2 - N(B1)
3​	5​	$ 29.50​	$ (0.50)​
4​	10​	$ 29.00​	$ (0.50)​
5​	15​	$ 28.50​	$ (0.50)​
6​	20​	$ 28.00​	$ (0.50)​
7​
8​	Hrs​	Amt​
9​	5​	$ 150.00​		B9: = SUMPRODUCT((A9 > $A$2:$A$6) * (A9 - $A$2:$A$6) * $C$2:$C$6)
10​	6​	$ 179.50​
11​	7​	$ 209.00​
12​	8​	$ 238.50​
13​	9​	$ 268.00​
14​	10​	$ 297.50​
15​	11​	$ 326.50​
16​	12​	$ 355.50​
17​	13​	$ 384.50​
18​	14​	$ 413.50​
19​	15​	$ 442.50​
20​	16​	$ 471.00​
21​	17​	$ 499.50​
22​	18​	$ 528.00​
23​	19​	$ 556.50​
24​	20​	$ 585.00​
25​	21​	$ 613.00​
26​	22​	$ 641.00​
27​	23​	$ 669.00​
28​	24​	$ 697.00​
29​	25​	$ 725.00​