Help with Tier calculations

J

Jdogg2022

New Member
Joined
Mar 21, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I am trying to calculate when something has gone on detention.. as an example for my below table.. Days 1-7 woulod be free.. but then it goes into the next tier (8-14) which is when charges start incurring.. however it can only go to the maximum amount of days in that tier (which in this case is 7 days) before it goes to the next tier.. but keeping in mind that it is 7 days inclusive.. so once you hit 8 days of detention it should be 7 days detentention in the 8-14 catergory, and then 1 day in the 15-21 tier.

I will mention that the day to/from field has a vlookup running off it so it will change (these are already set up)
The grey box is where you would enter the amount of days detention has currently incurred.
The red cells are basically some formulars which I started to work on.

Will need some help on this please.

DAY FROMDAY TONumber of Days IncurredCost Incurred
8148 - 14 days6$480.00
152115 - 21 days8$880.00
2299922 days +0$0.00
Total
Number of days on Detention6$1,360.00
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.

Please post a couple of examples complete with expected results. Show the arithmetic that yields your results.
 
Upvote 0
HI Does this help,

Basically the cells in green are a moving target, but the number of days incurred are really stuffing me up. ie.. in the below case.. there should be 7 days in 8-14, and 7 days in 15-21 then the balance (51) should be in the last tier.
Does that make sense

Book1
NOPQRSTU
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
678148 - 14 days7$560.00$ 80.00<- Tier 2
77152115 - 21 days0$0.00$ 110.00<- Tier 3
89782299922 days +58$8,120.00$ 140.00<- Tier 4
9
10Total
11Number of days on Detention65$8,680.00
Data Table New
Cell Formulas
RangeFormula
N6:N8N6=(P6-O6)+1
O6O6=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$402,9,FALSE)
P6P6=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$402,10,FALSE)
Q6Q6=VLOOKUP(S2,'[Mondiale SLine Detention Rates 2022.xlsx]Sheet1'!$A$29:$E$47,3,FALSE)
R6R6=IF(R11>(VLOOKUP(S2,'[Mondiale SLine Detention Rates 2022.xlsx]Sheet1'!$A$4:$E$17,3,FALSE)),(VLOOKUP(S2,'[Mondiale SLine Detention Rates 2022.xlsx]Sheet1'!$A$4:$E$17,3,FALSE)),R11)
S6:S8S6=T6*R6
T6T6=VLOOKUP($S$2&$S$4&$S$3&"2",$A$2:$L$402,11,FALSE)
O7O7=VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$402,9,FALSE)
P7P7=VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$402,10,FALSE)
Q7Q7=VLOOKUP(S2,'[Mondiale SLine Detention Rates 2022.xlsx]Sheet1'!$A$29:$E$47,4,FALSE)
R7R7=IF(R11<(N6+N7),(N7+N6)-R11,0)
T7T7=VLOOKUP($S$2&$S$4&$S$3&"3",$A$2:$L$402,11,FALSE)
O8O8=IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,9,FALSE)="",O7,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,9,FALSE)))
P8P8=IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,10,FALSE)="",P7,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,10,FALSE)))
Q8Q8=IFERROR(VLOOKUP(S2,'[Mondiale SLine Detention Rates 2022.xlsx]Sheet1'!$A$29:$E$47,5,FALSE),)
R8R8=IF(R11<O7,0,IF(AND(R11>=O7,R11<365),(R11-(R7+R6))))
T8T8=IF(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,4,FALSE)="",T7,(VLOOKUP($S$2&$S$4&$S$3&"4",$A$2:$L$402,11,FALSE)))
S11S11=SUM(S6:S8)
Cells with Data Validation
CellAllowCriteria
R7Whole numberbetween 0 and N7
 
Upvote 0
I could guess but the usually is not a good idea.

1. We do not know your challenge or the background to your question.
2. We do not have your referenced sheet.
2. With a simple example, what are the rates etc, and what is the expected answer.
3. Is 65+6 your input number?
4. What is tier 1?
 
Upvote 0
Dave Patton said:
I could guess but the usually is not a good idea.

1. We do not know your challenge or the background to your question.
2. We do not have your referenced sheet.
2. With a simple example, what are the rates etc, and what is the expected answer.
3. Is 65+6 your input number?
4. What is tier 1?
Click to expand...
HI Dave, a very fair point,

Answers below

1) My challenge is trying to get the correct tier calculations
2) is there a way for me to update the whole sheet?
3) Not really worried about the rates at this stage as I have those calculated, just trying to get the number of days tier calculations working, So the Cells in red I am trying to calculate using Grey box vs O6 - P8 (if that makes any sense)
4) Input number is the box in Grey
5) Tier 1, is the free days (ie. no charge)

Does that help?

Regards
Joey Wright
 
Upvote 0
You can paste a copy of my post to a clean sheet.
Click on the icon below the f(x) in the heading and then move to your sheet and paste.
Please review the results with the formula shown below. Show a manual calculation if result is different.

Commission2022.xlsm
OPQRST
172
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
6
7070
871480
91421110
1021140
11
12Total
13Number of days on Detention65$8,470.00
14
15
16
17700
18780560
197110770
20511407140
218470
3a
Cell Formulas
RangeFormula
R13R13=O1-7
S13S13=SUMPRODUCT(--(O1>O7:O10),O1-O7:O10,T7:T10-T6:T9)
P20P20=O1-SUM(P17:P19)
R17:R20R17=P17*Q17
R21R21=SUM(R17:R20)
 
Last edited:
Upvote 0
Dave Patton said:
an paste a copy of my post to a clean sheet.
Click to expand...

Dave Patton said:
You can paste a copy of my post to a clean sheet.
Click on the icon below the f(x) in the heading and then move to your sheet and paste.
Please review the results with the formula shown below. Show a manual calculation if result is different.

Commission2022.xlsm
OPQRST
172
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
6
7070
871480
91421110
1021140
11
12Total
13Number of days on Detention65$8,470.00
14
15
16
17700
18780560
197110770
20511407140
218470
3a
Cell Formulas
RangeFormula
R13R13=O1-7
S13S13=SUMPRODUCT(--(O1>O7:O10),O1-O7:O10,T7:T10-T6:T9)
P20P20=O1-SUM(P17:P19)
R17:R20R17=P17*Q17
R21R21=SUM(R17:R20)
Click to expand...
HI Dave, I think you are pretty spot on and I cant believe it is pretty simple... however when I change the number of days on detention, the cost incurred doesnt change. Ie when I put say 12 days detention it doesnt calculate correct?
 
Upvote 0
If you review my formula, the input is O1. Edit the formula for your situation.
Excel 365 version of formula is also shown.
Commission2022.xlsm
OPQRS
112
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TOPer Day AmountNumber of Days IncurredCost Incurred
6
700
8780
914110
1021140
11
12Total
13Number of days on Detention5$400.00
14$400.00
15
16
17700
18580400
3a
Cell Formulas
RangeFormula
R13R13=O1-7
S13S13=SUMPRODUCT(--(O1>O7:O10),O1-O7:O10,Q7:Q10-Q6:Q9)
S14S14=SUM(IF(O1>O7:O10,(O1-O7:O10)*(Q7:Q10-Q6:Q9)))
R17:R18R17=P17*Q17
 
Upvote 0
Hi,

Based on your original set up, may be this will work also:

Book3.xlsx
NOPQRSTU
1
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
67814756080<- Tier 2
7715217770110<- Tier 3
897822999517140140<- Tier 4
9
10Total
11Number of days on Detention658470
Sheet1066
Cell Formulas
RangeFormula
R6R6=MIN(N6,R11)
S6:S8S6=T6*R6
R7R7=MIN(R11-R6,N7)
R8R8=MAX((R11-N6-N7),0)
S11S11=SUM(S6:S8)
 
Upvote 0
Solution
jtakw said:
Hi,

Based on your original set up, may be this will work also:

Book3.xlsx
NOPQRSTU
1
2Shipping LineCMA
3Container TypeGP
4Container Size20
5DAY FROMDAY TONumber of Days IncurredCost IncurredPer Day Amount
67814756080<- Tier 2
7715217770110<- Tier 3
897822999517140140<- Tier 4
9
10Total
11Number of days on Detention658470
Sheet1066
Cell Formulas
RangeFormula
R6R6=MIN(N6,R11)
S6:S8S6=T6*R6
R7R7=MIN(R11-R6,N7)
R8R8=MAX((R11-N6-N7),0)
S11S11=SUM(S6:S8)
Click to expand...
Ah this works perfectly, thank you so much!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Dynamic Tier System
2
Replies
18
Views
828
Dave Patton
D
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
153
staticfluids
staticfluids
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
357
Fluff
Fluff
McM_
  • Solved
Amount calculation in progress
Replies
4
Views
160
dreid1011
dreid1011
W
  • Question
Holiday hours Calculation
Replies
2
Views
452
Wrenn_6869
W

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

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