Custom rate prorating / Nested IFs?

[Lil Stinker]

@KRice

Post in thread 'Fixed minimum rate for prorating calculation'
Fixed minimum rate for prorating calculation

For the second requirement, I would still like to use the same proration table at the above link however, since this will be on a different form altogether, the formulas will probably need to change to fit the criteria.

On this form, rather than prorate a weekly rate by the day after a one week minimum is met, here, I am trying to have it prorate by the week, except the 2nd week is half of the first, the 3rd week half of the 2nd and the 4th week is free.

Example:
Unit Rate: $200
Rental term: 3 weeks, 4 days / Subtotal = $350
Rental term: > 4 weeks / Subtotal still = $350
Rental term: < 1 week / Subtotal = $200

I’ve managed to manipulate the existing formulas by changing what the weekly formula rounds to in columns Q & R and adding an additional column S to calculate the 3rd week. But this next part is where it gets complicated.

Monthly. The monthly proration can actually remain the same as it is currently: after a one month minimum is met, it would prorate by the weeks and days but needs to ignore the additional column S which doesn’t pertain to monthly calculations. I just can’t seem to figure out how to make the subtotal formula work to meet both the monthly and weekly criteria. I’ve tried using nested IFs but it only seems to take me so far then it falls apart when trying to meet the minimum rate for time periods less the required duration. I can provide examples tomorrow when I’m back in the office.

Basically, the subtotal column needs to account for a weekly rental prorated by half of the 1st week, then half of the 2nd week unless the duration equals a week or less, when the drop down (E17) calls for a Weekly term. It also needs to account for a monthly rental prorated by the weeks and days unless the duration equals a month or less, when the drop down (E17) calls for a Monthly term.

Finally, just to make it even more challenging, I would like to introduce a Custom option to the drop down. This Custom option would disregard any and all prorating formulas when calculating the Subtotal but would still display the accurate rental duration in months, weeks and days as the table is currently set to do. Basically, if a user selects Custom in the drop down, they’ll be responsible for inputting the subtotal manually.

Is it even possible for one cell to process all of those variations? Nested IFs or some other way?

 

[Lil Stinker]

Here are my examples where I've manipulated the existing Subtotal formula (T2) as much as my limited understanding of Excel can take me. It's close but not close enough because it does not work for all scenarios.

In this example, it is calculating correctly for a Weekly term because it is calculating the rate for 3 weeks correctly and zeroing out the 4th week as intended:

ORDER FORM -formula testing- 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Description	Column2	QTY	Unit Rate	Weeks	Item Subtotal		Terms: Weeks	Months	Weeks	Days	Months	Weeks	Days	Monthly Rate	Weekly Rate	Week 2	Week 3+	Subtotal	Rental Period
2	Billing Start	Something		1	200.00	3w,6d	$350.00		Weeks	0	3	6	0	3	6		200	100	50	350	3w,6d
3	9/5/2022
4	Billing End
5	10/1/2022
Sheet1

Cell Formulas
Range	Formula
I1	I1	="Terms: "&$F1
F2:F5	F2	=IF($F$1="[ Term ]","",IF($F$1="Months",U2,IF($F$1="Weeks",U2,IF($F$1="Days",U2))))
G2:G5	G2	=T2
I2:I5	I2	=IF(ISBLANK(D2),"",SUBSTITUTE(I$1,"Terms: ",""))
J2:J5	J2	=IF(ISBLANK(D2),"",IF(I2="Months",DATEDIF($A$3,$A$5+1,"M"),0))
K2:K5	K2	=IF(ISBLANK(D2),"",QUOTIENT(DATEDIF(EDATE($A$3,J2),$A$5+1,"D"),7))
L2:L5	L2	=IF(ISBLANK(D2),"",DATEDIF(EDATE($A$3,J2),$A$5+1,"D")-7*K2)
M2:M5	M2	=IF(ISBLANK(D2),"",IF(AND(I2="Months",K2=4),J2+1,J2))
N2:N5	N2	=IF(ISBLANK(D2),"",IF(AND(I2="Months",K2=4),0,K2))
O2:O5	O2	=IF(ISBLANK(D2),"",IF(AND(I2="Months",K2=4),0,L2))
P2:P5	P2	=IF(ISBLANK(D2),"",IF(I2="Months",E2,""))
Q2:Q5	Q2	=IF(ISBLANK(D2),"",IF(I2="Months",ROUND(E2/4,2),E2))
R2,R5	R2	=IF(ISBLANK(D2),"",ROUND(Q2/2,2))
S2,S5	S2	=IF(ISBLANK(E2),"",IF(I2="Months","",(ROUND(R2/2,2))))
T2	T2	=IF(ISBLANK(D2),"",D2*MAX(IF(I2="Months",SUMPRODUCT(M2:O2,P2:R2),SUMPRODUCT(Q2:S2)),IF(I2="Months",P2),IF(I2="Weeks",Q2)))
U2:U5	U2	=IF(ISBLANK(D2),"",IF(M2<>0,M2&IF(AND(N2=0,O2=0),"m","m,"),"")&IF(N2<>0,N2&IF(O2=0,"w","w,"),"")&IF(O2<>0,O2&"d",""))
T3:T4	T3	=IF(ISBLANK(D3),"",D3*MAX(IF(I3="Months",P3,Q3),SUMPRODUCT(M3:O3,P3:R3)))
R3:R4	R3	=IF(ISBLANK(D3),"",(ROUND(Q3/7,2)))
T5	T5	=IF(ISBLANK(D5),"",D5*MAX(IF(I5="Months",P5,SUMPRODUCT(M5:O5,P5:R5)),IF(I5="Weeks",Q5,SUM(Q5:S5))))

Cells with Data Validation
Cell	Allow	Criteria
F1	List	[ Term ],Months,Weeks,Days
C2:C5	Any value

However, when I change the duration to less than a week, it is still adding up the weeks rather than referring to cell Q2 as it should for a one week minimum rate, $200:

	Description	Column2	QTY	Unit Rate	Weeks	Item Subtotal		Terms: Weeks	Months	Weeks	Days	Months	Weeks	Days	Monthly Rate	Weekly Rate	Week 2	Week 3+	Subtotal	Rental Period
Billing Start	Something		1	200.00	4d	$350.00		Weeks	0	0	4	0	0	4		200	100	50	350	4d
9/5/2022
Billing End
9/8/2022

When I switch the term to Monthly in the drop down and leave the duration at less than a week, here it correctly shows the Sub to equal the $200 rate, which in this case is the one month minimum rate:

	Description	Column2	QTY	Unit Rate	Months	Item Subtotal		Terms: Months	Months	Weeks	Days	Months	Weeks	Days	Monthly Rate	Weekly Rate	Week 2	Week 3+	Subtotal	Rental Period
Billing Start	Something		1	200.00	4d	$200.00		Months	0	0	4	0	0	4	200.00	50	25		200	4d
9/5/2022
Billing End
9/8/2022

However, when I change the duration to be longer than a week but less than a month, it doesn't stay with the $200 one month minimum rate as it should but instead calculates based on the conditional columns (M:O) and displaying the incorrect sub:

	Description	Column2	QTY	Unit Rate	Months	Item Subtotal		Terms: Months	Months	Weeks	Days	Months	Weeks	Days	Monthly Rate	Weekly Rate	Week 2	Week 3+	Subtotal	Rental Period
Billing Start	Something		1	200.00	3w,6d	$300.00		Months	0	3	6	0	3	6	200.00	50	25		300	3w,6d
9/5/2022
Billing End
10/1/2022

It's a mess. I'm a mess. I need help!

 

[KRice]

I’ve managed to manipulate the existing formulas by changing what the weekly formula rounds to in columns Q & R and adding an additional column S to calculate the 3rd week. But this next part is where it gets complicated.

I wouldn't do it this way. Instead, this formula keeps the evaluation of the number of weeks in an array. We can go back to one part of the Original Num Weeks formula and compute the number of days for the rental (yes, I know this is a weekly rate...but this is easier, I think). Then we check the number of days to see if they are greater than 0,7,14,21 days, which is equivalent to determining if the rental term is at least 1 wk?, 2 wks?, 3 wks?, and 4 wks? So we'll get an array of TRUE's and FALSE's, which we then multiply by {1,0.5,0.25,0} to give us the fraction of the weekly base rate for each of those weekly sub-elements. Then we multiply that array by the base rate and sum.

=IF(I18="Weeks",SUM(Q18*(DATEDIF(EDATE($A$21,J18),$A$23+1,"D")>{0,7,14,21})*{1,0.5,0.25,0}),"")

So we can splice the above formula into the existing Subtotal to handle the "weeks" calculation while leaving everything else untouched. I'll have a look at that and your other "custom" option.

 

[KRice]

Give this a try to see if it does what you want. This is version 3, based on the most recent change to version 2 I posted in the other thread. This version includes the formula splice I mentioned above and accounts for the "custom" option. Only the green cells have been updated, and the column I formulas to pull over the user selection are manual entries for now (for the color-shaded cells).

Mrexcel_20220915_Lil Stinker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
14					introduces four options: months, weeks, days, custom				carries over user selection for months, weeks, days, custom	if "months" then floor of date range (start to end) in months else 0 (correct for weeks and days)	if "days" then 0 else QUOTIENT(date range from start + NumMonths to end in days, 7) (correct for months and weeks)	date range from start + NumMonths to end in days - 7*NumWeeks (correct for months, weeks, and days)	if "months" AND NumWeeks=4 then NumMonths+1 else NumMonths	if "months" AND NumWeeks=4 then 0 else NumWeeks	if "days" then MAX(30,actual days) else if "months" AND NumWeeks=4 then 0 else NumDays	if "months" then use monthly "unit rate" else "" (for selection of weeks and days)	if "months" then base weekly rate on monthly rate/4 else if "weeks" then "unit rate" else "" (applies to selection "days")	if "days" then use daily rate directly else base daily rate on weekly rate/7 (applies to selection months and weeks). Also introduced logic check for "custom" to return ""	enforces minimum charge stipulations: if "Months" then min chg of 1m else if "weeks" then min chg of 1w else if "days then min chg of 30d. Min chg is compared to standard subtotal computed with SUM({m,w,d} periods * {m,w,d} rates). Also spliced in array-based formula to compute week by week discounts for "weeks" selection.
15
16										Original			Conditional
17	Date Range	Description	Qty	Unit Rate	Weeks	Item Subtotal			Terms: Weeks	Num Months	Num Weeks	Num Days	Num Months	Num Weeks	Num Days	Monthly Rate	Weekly Rate	Daily Rate	Subtotal	Rental Period
18		Computer Monitor	1	200	3w				Custom	0	3	0	0	3	0					3w
19		Speaker Set	1	6.67	3w	6.67			Months	0	3	0	0	3	0	6.67	1.67	0.24	6.67	3w
20	Start Date	Keyboard & Mouse Set	1	6.67	3w	20.01			Weeks	0	3	0	0	3	0		6.67	0.95	20.01	3w
21	9/14/22	Computer Tower	1	6.67	3w	20.01			Days	0	3	0	0	3	0		6.67	0.95	20.01	3w
22	End Date	Red Stapler	1	6.67	3w	20.01			Weeks	0	3	0	0	3	0		6.67	0.95	20.01	3w
23	10/4/22	Red Stapler	1	6.67	3w	20.01			Weeks	0	3	0	0	3	0		6.67	0.95	20.01	3w
24	inclusive	Red Stapler	1	6.67	3w	20.01			Weeks	0	3	0	0	3	0		6.67	0.95	20.01	3w
25
26
27					Subtotal	106.72
28					Discount	10.0%
29					Net Amount	96.05
30					TOTAL AMOUNT	96.05
Lil Stinker_v3

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18	K18	=IF(ISBLANK(C18),"",IF(I18="Days",0,QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23+1,"D"),7)))
L18:L26	L18	=IF(ISBLANK(C18),"",DATEDIF(EDATE($A$21,J18),$A$23+1,"D")-7*K18)
M18:M26	M18	=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),J18+1,J18))
N18:N26	N18	=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,K18))
O18	O18	=IF(ISBLANK(C18),"",IF(I18="Days",MAX(L18,30),IF(AND(I18="Months",K18=4),0,L18)))
P18:P26	P18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18	Q18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),IF(I18="Weeks",D18,"")))
R18	R18	=IF(ISBLANK(C18),"",IF(I18="Custom","",IF(I18="Days",D18,(ROUND(Q18/7,2)))))
S18	S18	=IF(ISBLANK(C18),"",IF(I18="Custom","",C18*MAX(IF(I18="Months",P18,IF(I18="Weeks",Q18,30*R18)), IF(I18="Weeks",SUM(Q18*(DATEDIF(EDATE($A$21,J18),$A$23+1,"D")>{0,7,14,21})*{1,0.5,0.25,0}), SUMPRODUCT(M18:O18,P18:R18) ))))
T18:T26	T18	=IF(ISBLANK(C18),"",IF(M18<>0,M18&IF(AND(N18=0,O18=0),"m","m,"),"")&IF(N18<>0,N18&IF(O18=0,"w","w,"),"")&IF(O18<>0,O18&"d",""))
K19:K26	K19	=IF(ISBLANK(C19),"",QUOTIENT(DATEDIF(EDATE($A$21,J19),$A$23+1,"D"),7))
O19:O26	O19	=IF(ISBLANK(C19),"",IF(AND(I19="Months",K19=4),0,L19))
Q19:Q26	Q19	=IF(ISBLANK(C19),"",IF(I19="Months",ROUND(D19/4,2),D19))
R19:R26	R19	=IF(ISBLANK(C19),"",(ROUND(Q19/7,2)))
S19:S26	S19	=IF(ISBLANK(C19),"",C19*MAX(IF(I19="Months",P19,Q19),SUMPRODUCT(M19:O19,P19:R19)))
I22:I26	I22	=IF(ISBLANK(C22),"",SUBSTITUTE(I$17,"Terms: ",""))
E18:E26	E18	=T18
F18:F26	F18	=S18
F27	F27	=SUBTOTAL(109,F18:F26)
F29	F29	=F27*(1-F28)
F30	F30	=F29

Cells with Data Validation
Cell	Allow	Criteria
E17	List	Months,Weeks,Days,Custom

 

[Lil Stinker]

Once again, you prove to be a maestro. I completely understand your explanation of adding in arrays, but I still can't comprehend how it actually works. It's like magic! I will put this through more rigorous testing over the next week to see if any abnormalities pop up. Otherwise, aces! Great stuff!

Thanks again!

 

[KRice]

You're welcome...I'm happy to help. Let me know if my post of a few minutes ago comes through. I saw a post of yours from 5:13 PM today but I don't see it above. I responded to that post with a slight tweak to the Conditional Num Days formula (to address your comment about rounding days). If you don't see that response, I'll repost.