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
<tbody>
</tbody>
Example Table with Formulas Showing
<tbody>
</tbody>
Any help with this would be much appreciated
Cheers
Craig
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