Help with Tier calculations

[Jdogg2022]

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 FROM	DAY TO		Number of Days Incurred	Cost Incurred
8	14	8 - 14 days	6	$480.00
15	21	15 - 21 days	8	$880.00
22	999	22 days +	0	$0.00
				Total
		Number of days on Detention	6	$1,360.00

 

[jtakw]

You're welcome, thanks for the feedback.

 

[Dave Patton]

Why do you want the calculation for each bracket?
The following assumes that the first bracket will always have a rate of 0 and the number on detention does not include the first 7 days.
You want the input to be later in the spreadsheet than the calculations.
If the rates are constant, you can name the rate differentials. I named the rate differentials aR value ={80;30;30}

Commission2022.xlsm
	O	P	Q	R	S	T	U
1				Shipping Line	CMA
2				Container Type	GP
3				Container Size	20	--- Arithmetic ---
4	DAY FROM	DAY TO	Per Day Amount	Number of Days Incurred	Cost Incurred	By Bracket	Cumulative
5
6	0		80			560.00	560.00
7	7		110			770.00	1,330.00
8	14		140			7,140.00	8,470.00
9	1E+308
10
11					Total
12			Number of days on Detention	65	$8,470.00	$8,470.00
13				if rates are constant	$8,470.00	$8,470.00
14
15
3a

Cell Formulas
Range	Formula
T6:T8	T6	=MAX(0,MIN($R$12,O7)-O6)*Q6
U6	U6	=MAX(0,MIN($R$12,O7)-O6)*Q6+N(U4)
U7:U8	U7	=MAX(0,MIN($R$12,O8)-O7)*Q7+N(U6)
O9	O9	=BigNum
S12	S12	=SUMPRODUCT(--(R12>O6:O8),R12-O6:O8,Q6:Q8-Q5:Q7)
T12	T12	=SUM(IF(R12>O6:O8,(R12-O6:O8)*(Q6:Q8-Q5:Q7)))
S13	S13	=SUMPRODUCT(--(R12>O6:O8),R12-O6:O8,aR)
T13	T13	=SUM(IF(R12>O6:O8,(R12-O6:O8)*(Q6:Q8-Q5:Q7)))