Formula Help - Daily charges - Increasing after 4 days

[Vansar]

Hello all,

I am struggling with writing a formula that will calculate a fine dependant on the road type, however some of the road attract higher charges after 3 days, eg: 0 T/S 5 day overrun = 3x5000, 2 x 10000 = 35000, i do have a column that works out my working days overrun by checking against a table =IF(O2="","",(IFERROR(NETWORKDAYS(O2+1,P2,Holidays!$A$1:$A$96),"")))

I have tried using a Vlookup but cannot work out how to trigger the 4th day higher rate but also multiply the first 3 days correctly

V-Lookup Table

[TABLE="width: 200"]
<tbody>[TR]
[TD]Road[/TD]
[TD]Amount[/TD]
[TD]4th Day+[/TD]
[/TR]
[TR]
[TD]0 T/S[/TD]
[TD]£5,000.00[/TD]
[TD]£10,000.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]£2,500.00[/TD]
[TD]£2,500.00[/TD]
[/TR]
[TR]
[TD]1 T/S[/TD]
[TD]£5,000.00[/TD]
[TD]£10,000.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]£2,500.00[/TD]
[TD]£2,500.00[/TD]
[/TR]
[TR]
[TD]2 T/S[/TD]
[TD]£3,000.00[/TD]
[TD]£8,000.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]£2,000.00[/TD]
[TD]£2,000.00[/TD]
[/TR]
[TR]
[TD]3 T/S[/TD]
[TD]£750.00[/TD]
[TD]£750.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]£250.00[/TD]
[TD]£250.00[/TD]
[/TR]
[TR]
[TD]4 T/S[/TD]
[TD]£750.00[/TD]
[TD]£750.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]£250.00[/TD]
[TD]£250.00[/TD]
[/TR]
</tbody>[/TABLE]

Example Table with Formulas Showing

[TABLE="width: 1204"]
<tbody>[TR]
[TD]Days Overrun[/TD]
[TD]Road Cat[/TD]
[TD]Notice End Date[/TD]
[TD]Date Cleared[/TD]
[TD]Pot Charge[/TD]
[TD]Uplift[/TD]
[/TR]
[TR]
[TD]=IF(C2="","",(IFERROR(NETWORKDAYS(C2+1,D2,Holidays!$A$1:$A$96),"")))[/TD]
[TD]0 T/S[/TD]
[TD]43557[/TD]
[TD]43567[/TD]
[TD][/TD]
[TD]=E2*0.125[/TD]
[/TR]
</tbody>[/TABLE]

Any help with this would be much appreciated

Cheers

Craig

 

[AlanY]

this will works out fine based on no of working days

Book1
	A	B	C
1	Road	Amount	4th Day+
2	0 T/S	£5,000.00	£10,000.00
3	0	£2,500.00	£2,500.00
4	1 T/S	£5,000.00	£10,000.00
5	1	£2,500.00	£2,500.00
6	2 T/S	£3,000.00	£8,000.00
7	2	£2,000.00	£2,000.00
8	3 T/S	£750.00	£750.00
9	3	£250.00	£250.00
10	4 T/S	£750.00	£750.00
11	4	£250.00	£250.00
12
13
14	0 T/S	5	35000
15	2 T/S	4	17000
16	4 T/S	3	2250
Sheet1

Cell Formulas
Range	Formula
C14	=VLOOKUP(A14,$A$1:$C$11,2,0)*MIN(B14,3)+VLOOKUP(A14,$A$1:$C$11,3,0)*MAX(B14-3,0)

 

[Vansar]

AlanY,

Many thanks for this, it has been pickling my head for a while

Thanks you again

Craig

 

[AlanY]

you're welcome