Fixed minimum rate for prorating calculation

[Lil Stinker]

Hello-
I'm looking for a way to fix in a minimum rate when calculating for monthly or weekly rental terms. In the samples below, I have the same table indicating the result for a monthly rental and a weekly rental based on a drop down in column E. The idea is the user will select Months or Weeks based on the term they are choosing for the rental when placing an order. Each term however, has a minimum rental rate regardless of which is chosen. Weekly rentals have a minimum 7-day rental while Monthly has a minimum one month rental. If a weekly rental is selected but the duration is less than 7 days, it should still total whatever weekly rate is listed in the Unit Rate column rather than breaking it down by the day as it currently does. Same for when a monthly rental is selected on the form, if the duration is less than a month, it should still total the monthly rate listed in column D. It is only after the minimum is met, does the rental start to prorate.

The two tables I have off to the right are there for calculating the proration for either rental term. I have it set up this way because this seems to produce the most accurate proration of rental rates for our needs. The problem is, it does not factor in a minimum rental period. So, I don't know if a more detailed IF statement in the Item Subtotal column is the way to go or if different formulas are needed in each of the calculation tables to factor in the minimum rental rate but I do know that I'm not smart enough to figure it out.

Any help would be greatly appreciated.

(By the way, this is meant to be used for drafting quotes to our clients. The start and end dates, equipment and rental rates are not fixed and will vary per quote).

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
17		Description	QTY	Unit Rate	Months	Item Subtotal		[ Term ]	Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal		Weeks	Days	Daily Rate	Subtotal
18		Computer Monitor	1	200.00	3w,6d	$192.84		Months	0.00	3.00	6.00	50.00	7.14	192.84		3.00	6.00	28.57	771.42
19		Speaker Set	2	50.00	3w,6d	$96.48		Weeks	0.00	3.00	6.00	12.50	1.79	96.48		3.00	6.00	7.14	385.68
20	Start Date	Keyboard & Mouse Set	1	20.00	3w,6d	$19.26			0.00	3.00	6.00	5.00	0.71	19.26		3.00	6.00	2.86	77.16
21	03/01/22	Computer Tower	1	500.00	3w,6d	$482.16			0.00	3.00	6.00	125.00	17.86	482.16		3.00	6.00	71.43	1928.58
22	End Date
23	03/28/22
24
25
26
27					Subtotal	$790.74
28					Discount	$0.00
29					Net Amount	$790.74
30					TOTAL AMOUNT	$790.74
31
32		Description	QTY	Unit Rate	Weeks	Item Subtotal		[ Term ]	Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal		Weeks	Days	Daily Rate	Subtotal
33		Computer Monitor	1	200.00	6d	$171.42		Months	0.00	0.00	6.00	50.00	7.14	42.84		0.00	6.00	28.57	171.42
34		Speaker Set	2	50.00	6d	$85.68		Weeks	0.00	0.00	6.00	12.50	1.79	21.48		0.00	6.00	7.14	85.68
35	Start Date	Keyboard & Mouse Set	1	20.00	6d	$17.16			0.00	0.00	6.00	5.00	0.71	4.26		0.00	6.00	2.86	17.16
36	04/01/22	Computer Tower	1	500.00	6d	$428.58			0.00	0.00	6.00	125.00	17.86	107.16		0.00	6.00	71.43	428.58
37	End Date
38	04/07/22
39
40
41
42					Subtotal	$702.84
43					Discount	$0.00
44					Net Amount	$702.84
45					TOTAL AMOUNT	$702.84
Sheet 2

Cell Formulas
Range	Formula
I18:I26	I18	=IF(ISBLANK(C18),"",DATEDIF($A$21,$A$23,"M"))
J18:J26	J18	=IF(ISBLANK(C18),"",INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7))
K18:K26	K18	=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
L33:L41,L18:L26	L18	=IF(ISBLANK(C18),"",(ROUND(D18/4,2)))
M33:M41,M18:M26	M18	=IF(ISBLANK(C18),"",(ROUND(L18/7,2)))
N33:N41,N18:N26	N18	=IF(ISBLANK(C18),"",(D18*I18+L18*J18+M18*K18)*C18)
P18:P26	P18	=IF(ISBLANK(C18),"",INT((DATEDIF($A$21,$A$23,"d")/7)))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
R33:R41,R18:R26	R18	=IF(ISBLANK(C18),"",(ROUND(D18/7,2)))
S33:S41,S18:S26	S18	=IF(ISBLANK(C18),"",(D18*P18+R18*Q18)*C18)
E18:E26	E18	=IF(OR($B18="",AND(E$17<>"Months",E$17<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$17="Months",DATEDIF($A$21,$A$23,"m")&"m,"&INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7)&"w,"&MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7)&"d", INT(($A$23-$A$21)/7)&"w,"&MOD($A$23-$A$21,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F18:F26	F18	=IF(ISBLANK(C18),"",IF(E$17="Months",(D18*I18+L18*J18+M18*K18)*C18,IF(E$17="Weeks",((D18*P18+R18*Q18)*C18))))
F27	F27	=SUBTOTAL(109,Table2261013[Item Subtotal])
L42:L45,L27:L30	L27	=IF(ISBLANK(C22),"",(ROUND(K27/7,2)))
M42:M45,M27:M30	M27	=IF(ISBLANK(C22),"",(D22*H27+K27*I27+L27*J27)*C22)
F29,F44	F29	=(F27-(F27*F28))
F30,F45	F30	=F29
I33:I41	I33	=IF(ISBLANK(C33),"",DATEDIF($A$36,$A$38,"M"))
J33:J41	J33	=IF(ISBLANK(C33),"",INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7))
K33:K41	K33	=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
P33:P41	P33	=IF(ISBLANK(C33),"",INT((DATEDIF($A$36,$A$38,"d")/7)))
Q33:Q41	Q33	=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
E33:E41	E33	=IF(OR($B33="",AND(E$32<>"Months",E$32<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$32="Months",DATEDIF($A$36,$A$38,"m")&"m,"&INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7)&"w,"&MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7)&"d", INT(($A$38-$A$36)/7)&"w,"&MOD($A$38-$A$36,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F33:F41	F33	=IF(ISBLANK(C33),"",IF(E$32="Months",(D33*I33+L33*J33+M33*K33)*C33,IF(E$32="Weeks",((D33*P33+R33*Q33)*C33))))
F42	F42	=SUBTOTAL(109,Table226101316[Item Subtotal])

Cells with Data Validation
Cell	Allow	Criteria
E17	List	=$H$1:$H$3
E32	List	=$H$1:$H$3

 

[Lil Stinker]

Yes, correct, exactly!

 

[KRice]

The issue with basing everything off of a monthly rate even when it is a weekly rental is the daily rate would end up being too low because it would calculate at 1/28th of the month rather than 1/7th of the week—if I’m understanding you correctly. Rather than having a $100 weekly rate equal $14.29 a day once it went over a week, the daily rate would be $3.57.

Yes...that was my point. Inputted "Monthly" rates essentially lead to weekly and daily rates that define a month as 4 weeks or 28 days, whereas inputted "Weekly" rates do not define a month this way (in fact, there is no month definition in this case, only weeks and days). So if inputted "Weekly" rates were determined in your lookup tables as "Monthly" rate/4 (they probably are not), there would never be any instance where a Weekly rate would be preferred over the Monthly rate. At best the two would be equivalent, and in many cases, the Monthly rate would be preferred. But after reading your response, I don't think this is an issue, as you've clarified that you have both Weekly rates and Monthly rates (and Weekly is probably somewhat greater than Monthly/4) and Monthly rates must apply to rental durations >=1 month...so those clarifications put this issue to rest.

Give this version a try. You should be able to click on the clipboard icon in the upper left (at intersection of rows and columns headings) and then paste into into cell A18 on some worksheet where you have room to accommodate it. The formulas should come along with this operation. I'm not sure if you need the XL2BB add-in installed for this feature, but give it a try to see. This version addresses the rounding clarification and requires "Months" to be specified...and that fixes the other issue I noticed and mentioned (about rounding) a few posts back. Let me know if you encounter any unexpected issues or the "7d" issue you described.

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
18										Original			Conditional
19	Date Range	Description	QTY	Unit Rate	Months	Item Subtotal			Terms: Months	Num Months	Num Weeks	Num Days	Num Months	Num Weeks	Num Days	Monthly Rate	Weekly Rate	Daily Rate	Subtotal	Rental Period
20		Computer Monitor	1	200	1m	200.00			Months	0	4	1	1	0	0	200.00	50.00	7.14	200.00	1m,0w,0d
21		Speaker Set	2	50	1m	100.00			Months	0	4	1	1	0	0	50.00	12.50	1.79	100.00	1m,0w,0d
22	Start Date	Keyboard & Mouse Set	1	20	1m	20.00			Months	0	4	1	1	0	0	20.00	5.00	0.71	20.00	1m,0w,0d
23	4/12/22	Computer Tower	1	500	1m	500.00			Months	0	4	1	1	0	0	500.00	125.00	17.86	500.00	1m,0w,0d
24	End Date
25	5/10/22
26
27
28
29					Subtotal	820.00
30					Discount	10.0%
31					Net Amount	738.00
32					TOTAL AMOUNT	738.00
Lil Stinker

Cell Formulas
Range	Formula
I19	I19	="Terms: "&$E19
I20:I28	I20	=IF(ISBLANK(C20),"",SUBSTITUTE(I$19,"Terms: ",""))
J20:J28	J20	=IF(ISBLANK(C20),"",IF(I20="Months",DATEDIF($A$23,$A$25+1,"M"),0))
K20:K28	K20	=IF(ISBLANK(C20),"",QUOTIENT(DATEDIF(EDATE($A$23,J20),$A$25+1,"D"),7))
L20:L28	L20	=IF(ISBLANK(C20),"",DATEDIF(EDATE($A$23,J20),$A$25+1,"D")-7*K20)
M20:M28	M20	=IF(ISBLANK(C20),"",IF(AND(I20="Months",K20=4),J20+1,J20))
N20:N28	N20	=IF(ISBLANK(C20),"",IF(AND(I20="Months",K20=4),0,K20))
O20:O28	O20	=IF(ISBLANK(C20),"",IF(AND(I20="Months",K20=4),0,L20))
P20:P28	P20	=IF(ISBLANK(C20),"",IF(I20="Months",D20,""))
Q20:Q28	Q20	=IF(ISBLANK(C20),"",IF(I20="Months",ROUND(D20/4,2),D20))
R20:R28	R20	=IF(ISBLANK(C20),"",(ROUND(Q20/7,2)))
S20:S28	S20	=IF(ISBLANK(C20),"",C20*SUMPRODUCT(M20:O20,P20:R20))
T20:T28	T20	=IF(ISBLANK(C20),"",M20&"m,"&N20&"w,"&O20&"d")
E20:E28	E20	=IF($B20="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T20,"0m,",""),"0w,",""),",0d",""))
F20:F28	F20	=S20
F29	F29	=SUBTOTAL(109,F20:F28)
F31	F31	=F29*(1-F30)
F32	F32	=F31

Cells with Data Validation
Cell	Allow	Criteria
E19	List	Months,Weeks

 

[KRice]

I reviewed your comments in posts 8,12, and 15. I think the latest version may be what you want, but I am not sure about the rounding. In your posts, you suggest that when the user chooses "Months", then any time the rental duration computation produces a M/W/D result where W=4, the result should be automatically rounded to (M+1)/0/0. This rule is implemented such that a "Months" rental from 7/1/2022 to 7/28/20220 is initially determined to be 0m4w0d, and finally resolved to be 1m. Similarly...

7/1/2022-7/28/2022	0m4w0d	1m
7/1/2022-7/29/2022	0m4w1d	1m
7/1/2022-7/30/2022	0m4w2d	1m
7/1/2022-7/31/2022	1m0w0d	1m
7/1/2022-8/1/2022	1m0w1d	1m,1d

So 4 weeks and a couple or fewer days (depending on the month) results in rounding the term up to a whole month, and the slightly better monthly rate gives a more favorable (for the customer) total compared to using the weekly/daily rates derived from the monthly rate. This rounding rule applies only to a user-specified "Months" input---a "Weeks" input gives different results, reported in terms of weeks and days with no rounding. I believe this is consistent with your description. This approach will not automatically change a "Weeks" rental into a "Months" rental because it has no way of knowing how to convert an input "Weeks" Unit Rate into a "Months" Unit Rate. The assumption is that the user-input Unit Rates apply only to the term selected in the column E heading---either "Weeks" or "Months". Please let me know if I've missed something or if you discover some unexpected/unwanted behavior.

 

[Lil Stinker]

Regarding the 1w, 7d issue, you beat me to the punch. I was just about to post the original formulas from Post #6 when I saw you had posted this new form. Near as I can tell, the only difference between the two forms is there was "+1" missing from the Num Weeks column K formula in the original that led to the error. Once I added in +1 to that formula, the error I received displaying 1w, 7d corrected to 2w.

Personally, when it comes to duplicating the formulas in my workbook, I prefer to manually copy them as opposed to copy/paste because it helps me attempt to understand the formula and commit at least some of it to memory... even when I have no clear clue as to how it all works.

For all intents and purposes, this new form seems to work perfectly regarding weekly/monthly dates and rounding to whole months. However, now it has lost the fixed minimum rate. Can I simply copy the original formula from the Subtotal column S to correct that issue or does that bring about more issues with the new layout? Now, any time the rental period is less than a week on weekly or less than a month on monthly, it's back to calculating by the weeks and days instead of locking in the minimum weekly or monthly rate.

 

[KRice]

That's good to hear. I went back to my earlier posts where "+1" was first introduced (that began in post #9)...and the +1 should appear in the column J,K, and L formulas. It sounds like you have that issue resolved.

As early as post #6, I had included MAX(IF(I18="Months",P18,Q18) in the Subtotal formula. But I recently deleted the MAX portion of the statement because I couldn't recall the original idea behind it. And now you've just reminded me Yes, adding that portion back in is fine. That enforces a minimum charge. The weekly rate (if "Weeks" is the basis for the estimate) or the monthly rate (if "Months") is compared to the actual SUMPRODUCT of rates and time breakdowns (m/w/d)...and the MAX is chosen, thereby ensuring a charge for a minimum of 1 week or 1 month. Here is the version incorporating this:

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
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	6d	200.00			Weeks	0	0	6	0	0	6		200.00	28.57	200.00	0m,0w,6d
19		Speaker Set	2	50	6d	100.00			Weeks	0	0	6	0	0	6		50.00	7.14	100.00	0m,0w,6d
20	Start Date	Keyboard & Mouse Set	1	20	6d	20.00			Weeks	0	0	6	0	0	6		20.00	2.86	20.00	0m,0w,6d
21	7/1/22	Computer Tower	1	500	6d	500.00			Weeks	0	0	6	0	0	6		500.00	71.43	500.00	0m,0w,6d
22	End Date
23	7/6/22
24	inclusive
25
26
27					Subtotal	820.00
28					Discount	10.0%
29					Net Amount	738.00
30					TOTAL AMOUNT	738.00
Lil Stinker

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
I18:I26	I18	=IF(ISBLANK(C18),"",SUBSTITUTE(I$17,"Terms: ",""))
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18:K26	K18	=IF(ISBLANK(C18),"",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:O26	O18	=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,L18))
P18:P26	P18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),D18))
R18:R26	R18	=IF(ISBLANK(C18),"",(ROUND(Q18/7,2)))
S18:S26	S18	=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",P18,Q18),SUMPRODUCT(M18:O18,P18:R18)))
T18:T26	T18	=IF(ISBLANK(C18),"",M18&"m,"&N18&"w,"&O18&"d")
E18:E26	E18	=IF($B18="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T18,"0m,",""),"0w,",""),",0d",""))
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

 

[Lil Stinker]

Fantastic! This is great work, Kirk! Thank you so much for your time and patience.
I will start putting this through more rigorous testing using quotes with varying rates, dates and rental terms today. I'll let you know if I come across anything unusual but otherwise, I think everything is spot on.

Thanks again!

 

[KRice]

That's great! Once you're satisfied that everything is working as expected, you may want to prevent editing on your cells containing formulas and protect the worksheet. Thanks for the good news...and I'm happy to help.

 

[Lil Stinker]

Just stumbled on an anomaly that I cannot figure out. It's a bit of devil's advocate because this isn't something we would ever do but when I plugged in Start Date: 8/2/22, End Date: 10/15/22 everything calculated as expected when E17 had "Months" selected. However, when I selected "Weeks" in E17, column E resulted in 15d, instead of 10w, 5d.

When I tried a different set of dates, specifically 5/24/22 to 10/18/22, and selected "Weeks", it correctly displayed 21w,1d.

Now, if E18:E26 is simply copying T18:T26 and substituting 0 values for blanks... where in the world did it decide to convert 10w,5d to 15d?

Like I said, it's not important because we would never quote anything above a month at a weekly term... I just found it bizarre.

 

[KRice]

Oh...I see the issue. The time components break down just fine. The only issue is the final formatting of the display where the SUBSTITUTE function is involved. 10w,5d --> 15d when the text string "0w," is stripped out and replaced with a blank...which is one of the things performed by the SUBSTITUTE formula. Similar problems will occur with 10m rentals (the 0m, will be removed), 10w or 20w or 30w rentals, etc. As you say, most (all?) of these aren't realistic because of how you structure the rentals, but it would be nice to clean this up. I'll have a closer look.

 

[KRice]

This version does all of the Rental Period text string construction in the T column. It's messy because it needs to determine whether to use a comma or not, and that depends on whether other values are present. Excel 365 has a nifty TEXTJOIN function that would make this much easier, but for now, this should work:

mrexcel_20220406.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
16										Original			Conditional
17	Date Range	Description	Qty	Unit Rate	Months	Item Subtotal			Terms: Months	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	4m,3w,4d	978.56			Months	4	3	4	4	3	4	200.00	50.00	7.14	978.56	4m,3w,4d
19		Speaker Set	2	50	4m,3w,4d	489.32			Months	4	3	4	4	3	4	50.00	12.50	1.79	489.32	4m,3w,4d
20	Start Date	Keyboard & Mouse Set	1	20	4m,3w,4d	97.84			Months	4	3	4	4	3	4	20.00	5.00	0.71	97.84	4m,3w,4d
21	8/2/22	Computer Tower	1	500	4m,3w,4d	2446.44			Months	4	3	4	4	3	4	500.00	125.00	17.86	2446.44	4m,3w,4d
22	End Date
23	12/26/22
24	inclusive
25
26
27					Subtotal	4012.16
28					Discount	10.0%
29					Net Amount	3610.94
30					TOTAL AMOUNT	3610.94
Lil Stinker

Cell Formulas
Range	Formula
I17	I17	="Terms: "&$E17
I18:I26	I18	=IF(ISBLANK(C18),"",SUBSTITUTE(I$17,"Terms: ",""))
J18:J26	J18	=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18:K26	K18	=IF(ISBLANK(C18),"",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:O26	O18	=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,L18))
P18:P26	P18	=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18:Q26	Q18	=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),D18))
R18:R26	R18	=IF(ISBLANK(C18),"",(ROUND(Q18/7,2)))
S18:S26	S18	=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",P18,Q18),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",""))
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