Dynamic Tier System

J

Jdogg2022

New Member
Joined
Mar 21, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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
OPQRSTU
1ContractQACD037777
2Shipping LineANL
3Container TypeHC
4Container Size40
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
6717$0.00$ -<- Tier 1
778147$770.00$ 110.00<- Tier 2
8715212$400.00$ 200.00<- Tier 3
9978229990$0.00$ 250.00<- Tier 4
10
11Total
12Number of days on Detention (Cell AB)9$1,170.00
13Number of Free Days12HC40
Data Table New Contract
Cell Formulas
RangeFormula
O6O6=Q6
Q6Q6=P7-1
S6:S7,S9S6=T6*R6
O7O7=(Q7-P7)+1
P7P7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,9,FALSE)
Q7Q7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,10,FALSE)
R7R7=MIN(O7,R12)
T7T7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,11,FALSE)
O8:O9O8=IFNA((Q8-P8)+1,0)
P8P8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,9,FALSE),0)
Q8Q8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,10,FALSE),0)
R8R8=MIN(R12-R7,O8)
S8S8=IFNA((T8*R8),0)
T8T8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,11,FALSE),0)
P9P9=(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))
Q9Q9=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)
R9R9=MAX((R12-O7-O8),0)
T9T9=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)
S12S12=SUM(S7:S9)
R12R12='C:\Users\joey.wright\Desktop\[detentionexport.xls]car_qry_detention'!$AB$17
R13R13=VLOOKUP($S$1,'Contract Numbers'!$B:$V,HLOOKUP('Data Table New Contract'!$U$13,'Contract Numbers'!$J$1:$V$2,2,FALSE),FALSE)
U13U13=S3&S4
Cells with Data Validation
CellAllowCriteria
R9Whole numberbetween 0 and O8
S2List='Data Table'!$M$3:$M$17
S3List='Data Table'!$N$3:$N$8
S4List='Data Table'!$O$3:$O$5
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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
PQRSTU
1
2
3
4Per Day Amount
5DAY FROM
600<- Tier 1
77110<- Tier 2
814200<- Tier 3
921250<- Tier 4
10
11Total
12Number of days on Detention (Cell AB)16
13
141170
15
1616 - 7 = 9
3aa
Cell Formulas
RangeFormula
R14R14=SUM((R12>P6:P9)*(R12-P6:P9)*(T6:T9-T5:T8))
 
Upvote 0
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
 
Upvote 0
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
PQRSTU
15
16DAY FROM
170110<- Tier 1
187200<- Tier 2
1914250<- Tier 3
20
2191,170.00
22
3aa
Cell Formulas
RangeFormula
R21R21=SUM((Q21>P17:P19)*(Q21-P17:P19)*(T17:T19-T16:T18))
 
Upvote 0
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
OPQRSTU
1ContractQAHQ046822-001
2Shipping LineANL
3Container TypeHC
4Container Size40
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
67170$0.00$ -<- Tier 1
778142$220.00$ 110.00<- Tier 2
8715217$1,400.00$ 200.00<- Tier 3
9978229997$1,750.00$ 250.00<- Tier 4
10
11Total
12Number of days on Detention (Cell AB)16$3,370.00
13Number of Free Days12HC40
Data Table New Contract
Cell Formulas
RangeFormula
O6O6=Q6
Q6Q6=P7-1
S6:S7,S9S6=T6*R6
O7O7=(Q7-P7)+1
P7P7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,9,FALSE)
Q7Q7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,10,FALSE)
O8:O9O8=IFNA((Q8-P8)+1,0)
P8P8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,9,FALSE),0)
Q8Q8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,10,FALSE),0)
P9P9=(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))
Q9Q9=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)
T7T7=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$366,11,FALSE)
S8S8=IFNA((T8*R8),0)
T8T8=IFNA(VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$366,11,FALSE),0)
T9T9=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)
S12S12=SUM(S7:S9)
R13R13=VLOOKUP($S$1,'Contract Numbers'!$B:$V,HLOOKUP('Data Table New Contract'!$U$13,'Contract Numbers'!$J$1:$V$2,2,FALSE),FALSE)
U13U13=S3&S4
Named Ranges
NameRefers ToCells
ANL='Contract Numbers'!$B$3:$B$55R13
APL='Contract Numbers'!$B$56:$B$60R13
CGMO='Contract Numbers'!$B$61:$B$68R13
COSCO='Contract Numbers'!$B$69:$B$94R13
HAMBURG='Contract Numbers'!$B$96:$B$103R13
HAPAG='Contract Numbers'!$B$104:$B$110R13
MAERSK='Contract Numbers'!$B$111:$B$147R13
MSC='Contract Numbers'!$B$150:$B$208R13
ONE='Contract Numbers'!$B$210:$B$251R13
OOCL='Contract Numbers'!$B$252:$B$271R13
PIL='Contract Numbers'!$B$272:$B$281R13
TSLINES='Contract Numbers'!$B$283:$B$286R13
ZIM='Contract Numbers'!$B$287:$B$290R13
Cells with Data Validation
CellAllowCriteria
S1List=INDIRECT($S$2)
S2List='Data Table'!$M$3:$M$17
S3List='Data Table'!$N$3:$N$8
S4List='Data Table'!$O$3:$O$5
 
Upvote 0
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?
 
Upvote 0
Dave Patton said:
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?
Click to expand...
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)
 
Upvote 0
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?
 
Upvote 0
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

ContractQAHQ046822-001
Shipping LineANL
Container TypeHC
FIXED BY SHIPPING LINEContainer Size40
DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
170$0.00$ -<- Tier 1
8142$220.00$ 110.00<- Tier 2
15217$1,400.00$ 200.00<- Tier 3
2299911$2,750.00$ 250.00<- Tier 4
Total
Number of days on Detention (Cell AB)20$4,370.00
Number of Free Days12HC40
 
Upvote 0
The brackets and your commentary are not consistent.
Can you provide a scenario where you consider just the chargeable days?



Commission2022.xlsm
PQRSTU
16DAY FROM
1700
1812110
1914200
2021250
21324,370.00
3aa
Cell Formulas
RangeFormula
R21R21=SUM((Q21>P17:P20)*(Q21-P17:P20)*(T17:T20-T16:T19))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Index and Match
Replies
3
Views
133
Peter_SSs
Peter_SSs
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
C
  • Question Question
Allocation Across Months
Replies
3
Views
492
NickFru
N
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
123
Mazurofgod
M
M
  • Question Question
VLOOKUP?
Replies
1
Views
222
MeaclH
M

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top