Dynamic Tier System

[Jdogg2022]

Hi All,

Really struggling with getting my Dynamic Tier System working

I've built a Shipping line detention free time calculator which works out the number of free days based on a specific Contract selected. (Each contract & Shipping line has its own specific number of free days)

Cell "S1" is the contract which is Dynamic and will publish the number of free days against that specific contract # in Cell "R13"
Cells "P1:Q9" are dynamic based on Shipping Line / Container Type / Container Size.. this works out my Tier System.

Cell "R12" is a cell which I would populate manually (as this is something I am going to run daily)..

Initially I was going to work it via Formulas, Then I started going down this rabbit hole of coding it via VBA.. but I cant seem to get the number of days incurred working correctly. In some instances the Number of Free days could be 7 , 12 , 14, 21, 30 free days.

Any advice would be appreciated.


There are only supposed to be a maximum amount of days in each tier, but I have to also incorporate the number of free days. Example. If there are 12 free days.. then Day #1 of detention would start on day 13. .

Mondiale SLine Detention Rates 2022.xlsx
	O	P	Q	R	S	T	U
1				Contract	QACD037777
2				Shipping Line	ANL
3				Container Type	HC
4				Container Size	40
5		DAY FROM	DAY TO	Number of Days Incurred	Cost Incurred	Per Day Amount
6	7	1	7		$0.00	$ -	<- Tier 1
7	7	8	14	7	$770.00	$ 110.00	<- Tier 2
8	7	15	21	2	$400.00	$ 200.00	<- Tier 3
9	978	22	999	0	$0.00	$ 250.00	<- Tier 4
10
11					Total
12			Number of days on Detention (Cell AB)	9	$1,170.00
13			Number of Free Days	12			HC40
Data Table New Contract

Cell Formulas
Range	Formula
O6	O6	=Q6
Q6	Q6	=P7-1
S6:S7,S9	S6	=T6*R6
O7	O7	=(Q7-P7)+1
P7	P7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,9,FALSE)
Q7	Q7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,10,FALSE)
R7	R7	=MIN(O7,R12)
T7	T7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,11,FALSE)
O8:O9	O8	=IFNA((Q8-P8)+1,0)
P8	P8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,9,FALSE),0)
Q8	Q8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,10,FALSE),0)
R8	R8	=MIN(R12-R7,O8)
S8	S8	=IFNA((T8*R8),0)
T8	T8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,11,FALSE),0)
P9	P9	=(IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE)="",P8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE))),0))
Q9	Q9	=IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE)="",Q8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE))),0)
R9	R9	=MAX((R12-O7-O8),0)
T9	T9	=IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE)="",T8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE))),0)
S12	S12	=SUM(S7:S9)
R12	R12	='C:\Users\joey.wright\Desktop\[detentionexport.xls]car_qry_detention'!$AB$17
R13	R13	=VLOOKUP($S$1,'Contract Numbers'!$B:$V,HLOOKUP('Data Table New Contract'!$U$13,'Contract Numbers'!$J$1:$V$2,2,FALSE),FALSE)
U13	U13	=S3&S4

Cells with Data Validation
Cell	Allow	Criteria
R9	Whole number	between 0 and O8
S2	List	='Data Table'!$M$3:$M$17
S3	List	='Data Table'!$N$3:$N$8
S4	List	='Data Table'!$O$3:$O$5

 

[Dave Patton]

Did you post the same question early in 2022?
We suggested formulas that would calculate the totals based on the tiers provided.
I am hesitant to post. You can review the logic of the suggestion.
N.B. We do not have the linked information.

Commission2022.xlsm
	P	Q	R	S	T	U
1
2
3
4					Per Day Amount
5	DAY FROM
6	0				0	<- Tier 1
7	7				110	<- Tier 2
8	14				200	<- Tier 3
9	21				250	<- Tier 4
10
11				Total
12		Number of days on Detention (Cell AB)	16
13
14			1170
15
16			16 - 7 = 9
3aa

Cell Formulas
Range	Formula
R14	R14	=SUM((R12>P6:P9)*(R12-P6:P9)*(T6:T9-T5:T8))

 

[Jdogg2022]

HI Dave,
Yes I posted something similar back in 2022,
Initially I got it sorted, however this time I have added another complexity and making it Dynamic which more variables is starting to get on my nerves.
Im happy to provide the sheet/file (if its possible).

Dropbox Link

I hope the Dropbox Linkworks

 

[Dave Patton]

It is probably better just to use the forum.
I can post an edit that just calculates for the 9.
I may be unavailable for a few weeks so please review my post asap.
The no charge units can be calculated but then to get to the results you cited we would start with 16. Per my first post.

Commission2022.xlsm
	P	Q	R	S	T	U
15
16	DAY FROM
17	0				110	<- Tier 1
18	7				200	<- Tier 2
19	14				250	<- Tier 3
20
21		9	1,170.00
22
3aa

Cell Formulas
Range	Formula
R21	R21	=SUM((Q21>P17:P19)*(Q21-P17:P19)*(T17:T19-T16:T18))

 

[Jdogg2022]

HI Dave,

I see where you are going with your post, however regret to inform it's not correct as the charges are accumulative, which is why i've put Day From - Day To.

Example.
Lets say I had something that has 12 free days. (Example Below)

Day 13 = 1st day of detention, and then in that particular tier, (Tier 2) there can only be a maximum of 7 days before the balance drops into Tier 3. Which means 2 days chargeable in Tier 2, then another 7 days chargeable in Tier 3, and the balance in Tier 4

SLine Detention Rates 2022 .xlsx
	O	P	Q	R	S	T	U
1				Contract	QAHQ046822-001
2				Shipping Line	ANL
3				Container Type	HC
4				Container Size	40
5		DAY FROM	DAY TO	Number of Days Incurred	Cost Incurred	Per Day Amount
6	7	1	7	0	$0.00	$ -	<- Tier 1
7	7	8	14	2	$220.00	$ 110.00	<- Tier 2
8	7	15	21	7	$1,400.00	$ 200.00	<- Tier 3
9	978	22	999	7	$1,750.00	$ 250.00	<- Tier 4
10
11					Total
12			Number of days on Detention (Cell AB)	16	$3,370.00
13			Number of Free Days	12			HC40
Data Table New Contract

Cell Formulas
Range	Formula
O6	O6	=Q6
Q6	Q6	=P7-1
S6:S7,S9	S6	=T6*R6
O7	O7	=(Q7-P7)+1
P7	P7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,9,FALSE)
Q7	Q7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,10,FALSE)
O8:O9	O8	=IFNA((Q8-P8)+1,0)
P8	P8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,9,FALSE),0)
Q8	Q8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,10,FALSE),0)
P9	P9	=(IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE)="",P8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,9,FALSE))),0))
Q9	Q9	=IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE)="",Q8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,10,FALSE))),0)
T7	T7	=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,11,FALSE)
S8	S8	=IFNA((T8*R8),0)
T8	T8	=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,11,FALSE),0)
T9	T9	=IFNA(IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE)="",T8,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$366,11,FALSE))),0)
S12	S12	=SUM(S7:S9)
R13	R13	=VLOOKUP($S$1,'Contract Numbers'!$B:$V,HLOOKUP('Data Table New Contract'!$U$13,'Contract Numbers'!$J$1:$V$2,2,FALSE),FALSE)
U13	U13	=S3&S4

Named Ranges
Name	Refers To	Cells
ANL	='Contract Numbers'!$B$3:$B$55	R13
APL	='Contract Numbers'!$B$56:$B$60	R13
CGMO	='Contract Numbers'!$B$61:$B$68	R13
COSCO	='Contract Numbers'!$B$69:$B$94	R13
HAMBURG	='Contract Numbers'!$B$96:$B$103	R13
HAPAG	='Contract Numbers'!$B$104:$B$110	R13
MAERSK	='Contract Numbers'!$B$111:$B$147	R13
MSC	='Contract Numbers'!$B$150:$B$208	R13
ONE	='Contract Numbers'!$B$210:$B$251	R13
OOCL	='Contract Numbers'!$B$252:$B$271	R13
PIL	='Contract Numbers'!$B$272:$B$281	R13
TSLINES	='Contract Numbers'!$B$283:$B$286	R13
ZIM	='Contract Numbers'!$B$287:$B$290	R13

Cells with Data Validation
Cell	Allow	Criteria
S1	List	=INDIRECT($S$2)
S2	List	='Data Table'!$M$3:$M$17
S3	List	='Data Table'!$N$3:$N$8
S4	List	='Data Table'!$O$3:$O$5

 

[Dave Patton]

Please refer to your post #1.
I showed how to arrive at the amounts you calculated, I added 7. The calculation then shows 0 on the first 7 of 16.
The alternative approach is to revise your brackets and rates to calculate just on the amount specified (not including the free one).
I showed this in the post above.

I have no knowledge of your business.
1. Do either of the suggestions help?
Why do they not work for you?

 

[Jdogg2022]

Please refer to your post #1.
I showed how to arrive at the amounts you calculated, I added 7. The calculation then shows 0 on the first 7 of 16.
The alternative approach is to revise your brackets and rates to calculate just on the amount specified (not including the free one).
I showed this in the post above.

I have no knowledge of your business.
1. Do either of the suggestions help?
Why do they not work for you?

Hi Dave,

Unfortunately, it doesn't help me.

For this example

Lets say the tier system is static from Days 0-7, 8-14, 15-21, 22-999.
You have 16 chargeable/billable days.
But you only have 12 days free.
That means your free days are used up in Tier 1 (0-7 days) and in tier 2 (8-14), But in tier 2, only 2 days remain which are "Chargeable"
Then in Tier 3, there is 7 days in that tier.. so all 7 days would be chargeable in that tier.. then the balance of days (7 days) would be pushed into the final tier.

Your billable days is broken up by the tier structure (0+ 2 + 7 + 7) = 16 days / {T1 + T2 + T3 + T4 = Billable amount)}

Does that make sense?

I need to have the From - To Tiers.. as the dates are dynamic depending on the shipping line. (as different shipping lines have different tier structures)

 

[Dave Patton]

You show bracket 1 7 days with rate of 0 but now you say there are 12 free days.
Your brackets and rates are not consistent with your commentary.

Please step away from the spreadsheet and provide a very simple scenario.

Say the stay is for 20 days. How would you calculate the charge?

What arithmetic would you use?

 

[Jdogg2022]

Well noted, I should mention that that the tier structure is fixed per shipping line with their own set tiers. But the Free Days can change depending on the contract.

Consider your 20 days = number of days on detention but this particular contract has 12 free days.

It would normally read as below

		Contract	QAHQ046822-001
		Shipping Line	ANL
		Container Type	HC
FIXED BY SHIPPING LINE		Container Size	40
DAY FROM	DAY TO	Number of Days Incurred	Cost Incurred	Per Day Amount
1	7	0	$0.00	$ -	<- Tier 1
8	14	2	$220.00	$ 110.00	<- Tier 2
15	21	7	$1,400.00	$ 200.00	<- Tier 3
22	999	11	$2,750.00	$ 250.00	<- Tier 4
			Total
	Number of days on Detention (Cell AB)	20	$4,370.00
	Number of Free Days	12			HC40

 

[Dave Patton]

The brackets and your commentary are not consistent.
Can you provide a scenario where you consider just the chargeable days?

Commission2022.xlsm
	P	Q	R	S	T	U
16	DAY FROM
17	0				0
18	12				110
19	14				200
20	21				250
21		32	4,370.00
3aa

Cell Formulas
Range	Formula
R21	R21	=SUM((Q21>P17:P20)*(Q21-P17:P20)*(T17:T20-T16:T19))