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
 
Sort by date Sort by votes
The calculation considering 12 free days and the original brackets is consistent with normal calculation and a "Discount" of 12-7 * 110

Commission2022.xlsm
PQRSTU
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)32
13
144,920.00Discount >5504,370.00
15
16DAY FROM
1700
1812110
1914200
2021250
21324,370.00
3aa
Cell Formulas
RangeFormula
R14R14=SUM((R12>P6:P9)*(R12-P6:P9)*(T6:T9-T5:T8))
T14T14=(12-7)*110
U14U14=R14-T14
R21R21=SUM((Q21>P17:P20)*(Q21-P17:P20)*(T17:T20-T16:T19))
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I am using Excel 2016, so the formula-based solution for this requirement is very complex and lengthy.
Therefore, I suggest using a User Defined Formula (UDF) to solve it.
The syntax is as follows:
=detention(Contract, Line, Type, Size, Day)
With your sample sheet, it should be:
=detention(S1, S2, S3, S4, R12)
Note that the reference range is fixed to range B1:Kn on the same sheet.

SLine Detention Rates 2022 .xlsm

drive.google.com drive.google.com
How to use: If you are new VBA user, try follow these steps:
1) Alt-F11 to open VBA window
2) Insert / Module, paste below code into

VBA Code: 
Option Explicit
Function Detention(ByVal Contract As Range, ByVal Line As Range, ByVal Typ As Range, ByVal Size As Range, day As Range)
Dim lr&, i&, j&, free&, tota As Double, min As Double, cost As Double, rng, idA As String, id As String
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("B2:K" & lr).Value
idA = Line & "|" & Typ & "|1"
free = Evaluate("=INDEX('Contract Numbers'!$J$3:$V$500, MATCH(S1,'Contract Numbers'!$B$3:$B$500,0),MATCH(S3&S4,'Contract Numbers'!$J$1:$V$1,0))")
For i = 1 To UBound(rng)
    id = rng(i, 1) & "|" & rng(i, 2) & "|" & IIf(Size = 20, rng(i, 3), rng(i, 4))
    If idA = id Then
        If rng(i, 8) = 1 Then min = rng(i, 10)
        For j = rng(i, 8) To IIf(rng(i, 9) < day + free, rng(i, 9), day + free)
            If j <= free Then cost = min Else cost = rng(i, 10)
            tota = tota + cost
        Next
    End If
Next
Detention = tota
End Function
 
Upvote 0
Solution
bebo021999 said:
I am using Excel 2016, so the formula-based solution for this requirement is very complex and lengthy.
Therefore, I suggest using a User Defined Formula (UDF) to solve it.
The syntax is as follows:
=detention(Contract, Line, Type, Size, Day)
With your sample sheet, it should be:
=detention(S1, S2, S3, S4, R12)
Note that the reference range is fixed to range B1:Kn on the same sheet.

SLine Detention Rates 2022 .xlsm

drive.google.com drive.google.com
How to use: If you are new VBA user, try follow these steps:
1) Alt-F11 to open VBA window
2) Insert / Module, paste below code into

VBA Code: 
Option Explicit
Function Detention(ByVal Contract As Range, ByVal Line As Range, ByVal Typ As Range, ByVal Size As Range, day As Range)
Dim lr&, i&, j&, free&, tota As Double, min As Double, cost As Double, rng, idA As String, id As String
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("B2:K" & lr).Value
idA = Line & "|" & Typ & "|1"
free = Evaluate("=INDEX('Contract Numbers'!$J$3:$V$500, MATCH(S1,'Contract Numbers'!$B$3:$B$500,0),MATCH(S3&S4,'Contract Numbers'!$J$1:$V$1,0))")
For i = 1 To UBound(rng)
    id = rng(i, 1) & "|" & rng(i, 2) & "|" & IIf(Size = 20, rng(i, 3), rng(i, 4))
    If idA = id Then
        If rng(i, 8) = 1 Then min = rng(i, 10)
        For j = rng(i, 8) To IIf(rng(i, 9) < day + free, rng(i, 9), day + free)
            If j <= free Then cost = min Else cost = rng(i, 10)
            tota = tota + cost
        Next
    End If
Next
Detention = tota
End Function
Click to expand...
Hi Bebo021999,

Im intrigued with your code and determined to get it working, Im fairly familiar with VBA however unsure on how I call / run your code.. Ive tried stepping through it (F8) but it doesnt seem to want to go. Can you help? (Sorry)
 
Upvote 0
Jdogg2022 said:
Hi Bebo021999,

Im intrigued with your code and determined to get it working, Im fairly familiar with VBA however unsure on how I call / run your code.. Ive tried stepping through it (F8) but it doesnt seem to want to go. Can you help? (Sorry)
Click to expand...
Sorry I think I got it working now (ended up downloading your spreadsheet.. Looks tidy! But wondering if the breakdown could be visible too?
 
Upvote 0
If 4370 is the result that you require, this formula provides your result.
You could define this as a function with Lambda.

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 32Free Days124,370.00
3aa
Cell Formulas
RangeFormula
U12U12=LET(b,P6:P9,r,T6:T9-T5:T8,SUM((R12>b)*(R12-b)*r)-SUM((T12>b)*(T12-b)*r))
 
Upvote 0
You can name the function something appropriate, the function shows the parameters required namely Total Days and Free Days.
The file does not need to be a macro enabled file.
This is a very simple Lambda function.
If any reader is actually interested, I will post the short line of the Named formula.

Commission2022.xlsm
V
124,370.00
3aa
Cell Formulas
RangeFormula
V12V12=Det_Total(R12,T12)
 
Upvote 0
Dave Patton said:
You can name the function something appropriate, the function shows the parameters required namely Total Days and Free Days.
The file does not need to be a macro enabled file.
This is a very simple Lambda function.
If any reader is actually interested, I will post the short line of the Named formula.

Commission2022.xlsm
V
124,370.00
3aa
Cell Formulas
RangeFormula
V12V12=Det_Total(R12,T12)
Click to expand...
Hi Dave,
I'm very interested in your Lambda function.. Im all for learning new functions and different methods of how to do things.

Appreciate your support and feedback as well!
 
Upvote 0
The Lambda specifies two parameters; Total Days "Tdays", and Free days "Fdays".
As currently written, the bracket array b is fixed and the rate array r is fixed.
Formula Name Manager
New specify a name I named the function Det_Total
Value =LAMBDA(Tdays,Fdays,LET(t,Tdays,f,Fdays,b,{0;7;14;21},r,{0;110;90;50},SUM((t>b)*(t-b)*r)-SUM((f>b)*(f-b)*r)))

Commission2022.xlsm
QRSTUV
12Number of days32Free Days124,370.004,370.00
13Number of days16Free Days71,170.001,170.00
14
3aa
Cell Formulas
RangeFormula
U12:U13U12=LET(b,$P$6:$P$9,r,$T$6:$T$9-$T$5:$T$8,SUM((R12>b)*(R12-b)*r)-SUM((T12>b)*(T12-b)*r))
V12:V13V12=Det_Total(R12,T12)
 
Upvote 0
A minor edit that just makes the function a little more concise.

Commission2022.xlsm
P
23=LAMBDA(Tdays,Fdays,LET(t,Tdays,f,Fdays,b,{0;7;14;21},r,{0;110;90;50},SUM((t>b)*(t-b)*r,-(f>b)*(f-b)*r)))
3aa
 
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