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

Road	Amount	4th Day+
0 T/S	£5,000.00	£10,000.00
0	£2,500.00	£2,500.00
1 T/S	£5,000.00	£10,000.00
1	£2,500.00	£2,500.00
2 T/S	£3,000.00	£8,000.00
2	£2,000.00	£2,000.00
3 T/S	£750.00	£750.00
3	£250.00	£250.00
4 T/S	£750.00	£750.00
4	£250.00	£250.00

<tbody>
</tbody>

Example Table with Formulas Showing

Days Overrun	Road Cat	Notice End Date	Date Cleared	Pot Charge	Uplift
=IF(C2="","",(IFERROR(NETWORKDAYS(C2+1,D2,Holidays!$A$1:$A$96),"")))	0 T/S	43557	43567		=E2*0.125

<tbody>
</tbody>

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