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...

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Hours[/TD]
[TD="width: 64"]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]28.5[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]28
[/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Hrs​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0​

[/td][td]

$ 30.00​

[/td][td="bgcolor:#CCFFCC"]

$ 30.00​

[/td][td="bgcolor:#CCFFCC"]C2: =B2 - N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

5​

[/td][td]

$ 29.50​

[/td][td="bgcolor:#CCFFCC"]

$ (0.50)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

10​

[/td][td]

$ 29.00​

[/td][td="bgcolor:#CCFFCC"]

$ (0.50)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

15​

[/td][td]

$ 28.50​

[/td][td="bgcolor:#CCFFCC"]

$ (0.50)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

20​

[/td][td]

$ 28.00​

[/td][td="bgcolor:#CCFFCC"]

$ (0.50)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#F3F3F3"]

Hrs​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

5​

[/td][td="bgcolor:#CCFFFF"]

$ 150.00​

[/td][td][/td][td="bgcolor:#CCFFFF"] B9: = SUMPRODUCT((A9 > $A$2:$A$6) * (A9 - $A$2:$A$6) * $C$2:$C$6) [/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

6​

[/td][td="bgcolor:#CCFFFF"]

$ 179.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

7​

[/td][td="bgcolor:#CCFFFF"]

$ 209.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

8​

[/td][td="bgcolor:#CCFFFF"]

$ 238.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

9​

[/td][td="bgcolor:#CCFFFF"]

$ 268.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

10​

[/td][td="bgcolor:#CCFFFF"]

$ 297.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

11​

[/td][td="bgcolor:#CCFFFF"]

$ 326.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

12​

[/td][td="bgcolor:#CCFFFF"]

$ 355.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

13​

[/td][td="bgcolor:#CCFFFF"]

$ 384.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

14​

[/td][td="bgcolor:#CCFFFF"]

$ 413.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

15​

[/td][td="bgcolor:#CCFFFF"]

$ 442.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

16​

[/td][td="bgcolor:#CCFFFF"]

$ 471.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

17​

[/td][td="bgcolor:#CCFFFF"]

$ 499.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

18​

[/td][td="bgcolor:#CCFFFF"]

$ 528.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

19​

[/td][td="bgcolor:#CCFFFF"]

$ 556.50​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

20​

[/td][td="bgcolor:#CCFFFF"]

$ 585.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

21​

[/td][td="bgcolor:#CCFFFF"]

$ 613.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

22​

[/td][td="bgcolor:#CCFFFF"]

$ 641.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

23​

[/td][td="bgcolor:#CCFFFF"]

$ 669.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]

24​

[/td][td="bgcolor:#CCFFFF"]

$ 697.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

25​

[/td][td="bgcolor:#CCFFFF"]

$ 725.00​

[/td][td][/td][td][/td][/tr]
[/table]