Nested Formula - Calculate cumulative charge based on value in reference cells

[DavePhil2]

Hi

I'm hoping someone can help me with the following nested formula issue.

What I would like to do is calculate a cumulative demurrage charge based on the values in two reference cells.
Formula to go into cell H34.
Reference cells are H7 (20FT/40FT) & H27 (calculated field - number of days).

If H7=20FT & H27=30, then the formula should work as follows
5 days @ €35 (D29) = €175, plus,
8 days @ €55 (D30) = €440, plus,
14 + days @ €66 (D31) = €1122.
SUMPRODUCT = €175 + €440 + €1122 = €1737

If H7=40FT & H27=30, then the formula should work as follows
5 days @ €60 (F29) = €300, plus,
8 days @ €100 (F30) = €800, plus,
14 + days @ €115 (F31) = €1955.
SUMPRODUCT = €300 + €800 + €1955 = €3055

Similarly, if the value in H27 fell between one on the early date ranges, the formula would only calculate those days.

Example 1

If H7=20FT & H27=10, then the formula should work as follows
5 days @ €35 (D29) = €175, plus,
5 days @ €55 (D30) = €275, plus,
SUMPRODUCT = €175 + €275 = €450

If H7=40FT & H27=10, then the formula should work as follows
5 days @ €60 (F29) = €300, plus,
5 days @ €100 (F30) = €500, plus,
SUMPRODUCT = €300 + €500 = €800

Example 2

If H7=20FT & H27=4, then the formula should work as follows
4 days @ €35 (D29) = €140,
SUMPRODUCT = €140

If H7=40FT & H27=4, then the formula should work as follows
4 days @ €60 (F29) = €240, plus,
SUMPRODUCT = €240

I hope the above makes sense.

thanks for your help in advance

 

[Dave Patton]

I calculated the charge in a formula.
The example in C3 names the Brackets and the Rate Differentials.
Name the information in Name Manager by looking at the information or reviewing the formula in B3.

Brackets aB ={0;5;13}
Rates r_20 ={35;20;11}
N.B. To post an extract of your sheet, you can use the forum's tool that is named XL2BB.

Commission2022.xlsm
	A	B	C	D	E	F
1	Days	50
2	Category	20
3	Amount	3,057.00	3,057.00
4
5
6	Brackets	Rates 20	--- Arithmetic ---			Rates 40
7	cell b7 is blank		By Bracket	Cumulative
8	0	35	175.00	175.00		60
9	5	55	440.00	615.00		100
10	13	66	2,442.00	3,057.00		115
11	1E+308
9a

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(--(B1>{0;5;13}),B1-A8:A10,B8:B10-B7:B9)
C3	C3	=SUMPRODUCT(--(B1>aB),(B1-aB),CHOOSE(MATCH(B2,{20,40},0),r_20,r_40))
C8:C10	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+N(D7)
D9:D10	D9	=MAX(0,MIN($B$1,A10)-A9)*B9+D8

 

[Dave Patton]

Please provide more information on the number of days calculations.

Commission2022.xlsm
	A	B	C	D
1	Days	10
2	Category	40
3	Amount		800.00
4
9a

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(B1>aB),(B1-aB),CHOOSE(MATCH(B2,{20,40},0),r_20,r_40))

 

[Kerryx]

Book1
	B	C	D	E	F	G	H
7							40FT
8
9
10
11
12
13
14
15
16
17	Min	Max	20FT	40FT
18	1	5	35	60
19	6	13	55	100
20	14		66	115
21
22
23
24
25
26
27						Days	4
28
29
30
31
32
33
34						Cost	240
Sheet1

Cell Formulas
Range	Formula
H34	H34	=IFS(H7="20FT",IF(H27<=5,H27*D18,5*D18+IF(H27-C18<=8,(H27-C18)*D19,8*D19)+IF(H27>=14,(H27-13)*D20,0)),H7="40FT",IF(H27<=5,H27*E18,5*E18+IF(H27-C18<=8,(H27-C18)*E19,8*E19)+IF(H27>=14,(H27-13)*E20,0)))

Cells with Data Validation
Cell	Allow	Criteria
H7	List	=$D$17:$E$17

 

[DavePhil2]

Sorry for the delay in replying.
thanks for all the responses.
@Kerryx, the above worked perfectly. Thank you.

 

[DavePhil2]

I have another variation of the above that I need some help with, please.

The same type of nested formula, just with a couple of extra variables.
Formula to go into cell H16.
Reference cells are H4 ("Dublin" or "Cork") & H7 ("20FT" or "40FT")
H14 (calculated field - number of days).

IF(AND(H4="Dublin",H7="20FT") then the formula should work as follows:
A. 0 - 6 days @ €30 (S3) plus,
B. 7 - 18 days @ €40 (S4) plus,
C. 19 - 46 days @ €80 (S5) plus,
D. 47 + days @ €120 (S6).
SUMPRODUCT = A + B + C + D

See the table in Q1 - T6.

IF(AND(H4="Dublin",H7="40FT") then the formula should work as follows:
A. 0 - 6 days @ €50 (T3) plus,
B. 7 - 18 days @ €70 (T4) plus,
C. 19 - 46 days @ €150 (T5) plus,
D. 47 + days @ €220 (T6).
SUMPRODUCT = A + B + C + D

See the table in V1 - Y6.

Example 1

H4="Dublin" AND H7="20FT"
H14 = 34 days

the formula should work as follows
6 days @ €30 (S3) = €180, plus,
12 days @ €40 (S4) = €480, plus,
16 days @ €80 (S5) = €1280
SUMPRODUCT = €180 + €480 + €1280 = €1940

H4="Dublin" AND H7="40FT"
H14 = 50 days

6 days @ €50 (T3) = €300, plus,
12 days @ €70 (T4) = €840, plus,
28 days @ €150 (T5) = €4200, plus,
4 days @ €220 (T6) = €880
SUMPRODUCT = €300 + €840 + €4200 + €880 = €6220

I hope the above makes sense.

thanks for your help in advance

 

[Kerryx]

cork..Dublin.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4								Dublin			Cork
5											Dublin
6
7								40FT
8
9
10
11
12
13
14
15
16
17	Loc	Min	Max	20FT	40FT
18	Cork	1	5	35	60
19	Cork	6	13	55	100
20	Cork	14		66	115
21	Dublin	1	6	30	50
22	Dublin	7	18	40	70
23	Dublin	19	46	80	150
24	Dublin	47		120	220
25
26
27							Days	50
28
29
30
31
32
33
34							Cost	6220
Sheet1

Cell Formulas
Range	Formula
H34	H34	=IFS(AND(H4="Cork",H7="20FT"),IF(H27<=5,H27*D18,5*D18+IF(H27-C18<=8,(H27-C18)*D19,8*D19)+IF(H27>=14,(H27-13)*D20,0)), AND(H4="Cork",H7="40FT"),IF(H27<=5,H27*E18,5*E18+IF(H27-C18<=8,(H27-C18)*E19,8*E19)+IF(H27>=14,(H27-13)*E20,0)),AND(H4="Dublin",H7="20FT"),IF(H27<=6,H27*D21,6*D21+IF(AND(H27-C21>=1,H27-C21<=12),(H27-C21)*D22,IF(H27>=18,12*D22,0))+IF(AND(H27-C22>=1,H27-C22<=28),(H27-C22)*D23,IF(H27>=46,28*D23,0)) +IF(H27>=47,(H27-C23)*D24,0)), AND(H4="Dublin",H7="40FT"),IF(H27<=6,H27*E21,6*E21+IF(AND(H27-C21>=1,H27-C21<=12),(H27-C21)*E22, IF(H27>=18,12*E22,0))+IF(AND(H27-C22>=1,H27-C22<=28),(H27-C22)*E23,IF(H27>=46,28*E23,0)) +IF(H27>=47,(H27-C23)*E24,0)))

Cells with Data Validation
Cell	Allow	Criteria
H4	List	=$K$4:$K$5
H7	List	=$D$17:$E$17

 

[Dave Patton]

see posts 2 and 3
This post considers your new tiers and rates

Commission2022.xlsm
	G	H
1
2
3
4		Dublin
5
6
7		40FT
8
9
10
11
12
13
14		50
15
16
17		6,220.00
18
9a

Cell Formulas
Range	Formula
H17	H17	=IF(H4="Dublin",SUMPRODUCT(--(H14>aB),(H14-aB),CHOOSE(MATCH(H7,{"20FT","40FT"},0),r_20,r_40)),IF(H4="York","Formula",""))

 

[Dave Patton]

Commission2022.xlsm
	H
4	Dublin
5
6
7	40FT
8
9
10
11
12
13
14	50
15
16
17	6,220.00
18
9a

Cell Formulas
Range	Formula
H17	H17	=IF(H4="Dublin",SUMPRODUCT(--(H14>d_B),(H14-d_B),CHOOSE(MATCH(H7,aL,0),d_R20,d_R40)),IF(H4="Cork",SUMPRODUCT(--(H14>c_B),(H14-c_B),CHOOSE(MATCH(H7,aL,0),c_R20,c_R40)),""))

 

[DavePhil2]

thank you so much, everyone.
this seems to be working correctly and has saved me a lot of time.
Much appreciated.