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]

These are great! Let me test them out and I'll let you know if I come across any issues. Otherwise, I'm fine with extra helper columns since they are technically hidden anyway.
Thanks again!

 

[Lil Stinker]

So the issue I’m seeing now with adding 1 to the daily formula is it skips whole week results. Instead of getting 1w, 2w, 3w and so on, the result I’m seeing is 1w, 7d. If I increase the duration by a day, then I get 2w, 1d. It never produces a whole week result when trying to include the end date in the total.

As for rounding to a whole month at 4 weeks, I inputted the conditional formulas and extra columns but it does not appear to actually affect the subtotal calculations. It is still breaking it down by the month, weeks and days. When I enter a time period that equates to 4 weeks even, it produces the monthly unit rate but when the time period comes to 4w, #d, it reverts to calculating by the day and week again rather than remaining fixed at the monthly unit rate.

 

[KRice]

Oh...I misunderstood something. The Subtotal refers (correctly, I think) to the newly introduced "Conditional" months/weeks/days, but it does not apply a different "base" rate. I thought the "Months" and "Weeks" input in the column E heading was supposed to control the relevant base rate. Are you saying that in the event "Weeks" is selected, and the number of weeks is >=4, then apply the Months rate (basically overriding the "Weeks" input)?

I'll look again at the +1 day issue you mentioned. What were the start/end dates where you saw the issue...and what were you expecting instead?

 

[KRice]

Can you give me an example where you see 1w, 7d? I haven't been able to reproduce that.

 

[Lil Stinker]

Oh...I misunderstood something. The Subtotal refers (correctly, I think) to the newly introduced "Conditional" months/weeks/days, but it does not apply a different "base" rate. I thought the "Months" and "Weeks" input in the column E heading was supposed to control the relevant base rate. Are you saying that in the event "Weeks" is selected, and the number of weeks is >=4, then apply the Months rate (basically overriding the "Weeks" input)?

The Months/Weeks drop down input in column E is just a manual option for the user to select depending on whether the rental is short term per a weekly basis or long term at a month or longer. It doesn’t affect the base rate. Rates are determined by the user. Generally, if it is more cost effective to quote a monthly rate versus a weekly rate, the user would then convert it manually and change the unit rate. Typically, a weekly rate would be abandoned if the rental extended beyond 3 weeks or so.

 

[Lil Stinker]

Can you give me an example where you see 1w, 7d? I haven't been able to reproduce that.

I won’t be in front of my sample tests until Monday but if I recall, I had a start date of 4/12/22 and end date 4/25/22 or 4/26/22. I basically incremented the end date one day at a time once I received the 1w,7d result. The next day increase, it skipped to 2w,1d. Only the original formula gave me a 2w result for the same date range (but as you mentioned, that formula only counts up to the end date and doesn’t include it).

 

[KRice]

Okay, check back in with me when you have a chance to illustrate the 7d issue. I have been unable to reproduce that result. I resolved the Subtotal issue you mentioned (I think), but I also identified another serious issue where "Weeks" is selected (so months should be zeroed), but the conditional logic sees weeks>=4 and adds 1 to months (which is incorrect). This is fixable with another tweak to the formulas to check whether "Months" or "Weeks" are selected. I'll look into this further, but between comments that led to the conditional columns and your post #15, I am somewhat confused. I initially understood that the user selection of Weeks/Months is absolute, and that the specific rates used for cost quoting were determined solely by that user-controlled selection:

1. If "Months", then express the rental duration in M/W/D and apply the corresponding M/W/D rates, or
2. If "Weeks", then zero out months and express the rental duration in W/D and apply the W/D rates.

Then the 4 wk rounding complicated matters and it hinted that there are some exceptions to the rule just described, where in some cases, a different rate might be applied. Post #15 seems to reinforce that the user tries to use whichever rate minimizes the total cost? So I am wondering if you would prefer to have the formulas determine which option (Months or Weeks) produces the cheapest Total Amount? Then the user wouldn't need to toggle between the two to figure that out. To do this, for any items that might appear in the Description field, I am thinking that you would enter a single "Rate", probably a monthly rate from which the weekly and daily rates are derived (that's what column Q and R do now, right?). Does this make sense? Right now, what do you do for the Unit Rate column? Do you manually input 200 for Computer Monitor (if monthly) or 50 (if weekly)? So do you use weekly rate = monthly rate/4 (as is done in column Q) for all of your column D inputs? If so, I think this idea might be easier to implement.

 

[KRice]

I think based on how I framed my questions, the monthly rate would always be preferred, since the weekly and daily rates are effectively 1/4th and 1/28th of the monthly rate. So the weekly/daily-only rates would produce, at best, a subtotal that is equal to that determined by the monthly rate, and in many cases would be more expensive than the monthly basis. I must not understand something fundamental about the Unit Rate inputs in column D. Do you always input a monthly rate for those? Under what circumstances would a weekly rate be preferred over a monthly rate?

 

[Lil Stinker]

Okay, check back in with me when you have a chance to illustrate the 7d issue. I have been unable to reproduce that result. I resolved the Subtotal issue you mentioned (I think), but I also identified another serious issue where "Weeks" is selected (so months should be zeroed), but the conditional logic sees weeks>=4 and adds 1 to months (which is incorrect). This is fixable with another tweak to the formulas to check whether "Months" or "Weeks" are selected. I'll look into this further, but between comments that led to the conditional columns and your post #15, I am somewhat confused. I initially understood that the user selection of Weeks/Months is absolute, and that the specific rates used for cost quoting were determined solely by that user-controlled selection:

1. If "Months", then express the rental duration in M/W/D and apply the corresponding M/W/D rates, or
2. If "Weeks", then zero out months and express the rental duration in W/D and apply the W/D rates.

This is correct. The selection of Weeks/Months is absolute. Unit Rates are determined by the salesperson based on their rate sheet. The client determines the duration of the rental based on their needs. Short term rentals less than 3 weeks are typically billed at a weekly rate therefore the quote would show ‘Weeks’ and the rate for any items would be on a weekly rate. Any rental at or over a month is billed at a monthly rate so the quote should reflect ‘Months’ along with monthly rates.

The gray area comes when a weekly rental extends to more than 3 weeks by a few days or so. If they know in advance that the rental will be for 3.5 weeks give or take, the salesperson will offer a monthly rate to save the client a few dollars even the though the rental will be less than a month. Rather than quote them at a weekly rate, they would just quote it for one month at the one month minimum price.

Then the 4 wk rounding complicated matters and it hinted that there are some exceptions to the rule just described, where in some cases, a different rate might be applied. Post #15 seems to reinforce that the user tries to use whichever rate minimizes the total cost? So I am wondering if you would prefer to have the formulas determine which option (Months or Weeks) produces the cheapest Total Amount? Then the user wouldn't need to toggle between the two to figure that out. To do this, for any items that might appear in the Description field, I am thinking that you would enter a single "Rate", probably a monthly rate from which the weekly and daily rates are derived (that's what column Q and R do now, right?). Does this make sense? Right now, what do you do for the Unit Rate column? Do you manually input 200 for Computer Monitor (if monthly) or 50 (if weekly)? So do you use weekly rate = monthly rate/4 (as is done in column Q) for all of your column D inputs? If so, I think this idea might be easier to implement.

The 4 week rounding only pertains to monthly quotes. The intention is to correct the anomaly that exists when a monthly rental that is being prorated in the M/W/D fashion as we have it, starts over billing by the day when in the second month or more. Every month on the calendar has at least 4 weeks and a day or so. Once we lock in the one month minimum in the calculations, the anomaly pops up starting in the second month where the additional days in the month after the 4 weeks are being added to the subtotal as opposed to simply rounding to a full month second month. It only occurs if the rental period comes to #months, 4weeks, #days. When we see something like this come up, we would just round it up to full months. Currently, the calculation fields are adding the extra days. Example: Unit Rate = $100/mo, Rental Period = 1m 4w 2d, Subtotal should = $200 instead, = $207.14

I would prefer the formulas not produce the more affordable option because I’m actually trying to keep this form as simple as it can be allowing for customization when needed. Sales people tend to stray outside the lines every once in a while, favoring certain clients, changing rates and so on. If we went in this automated direction, I’d have to create a rates table and equipment pricing table, add in more VLOOKUP formulas to produce the results. Maybe in version 2.0 but for now, I‘d rather keep the Unit Rate entries manual and the Weeks/Months decision up to the user. It’s an intriguing notion though.

I think based on how I framed my questions, the monthly rate would always be preferred, since the weekly and daily rates are effectively 1/4th and 1/28th of the monthly rate. So the weekly/daily-only rates would produce, at best, a subtotal that is equal to that determined by the monthly rate, and in many cases would be more expensive than the monthly basis. I must not understand something fundamental about the Unit Rate inputs in column D. Do you always input a monthly rate for those? Under what circumstances would a weekly rate be preferred over a monthly rate?

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.

We use weekly rates for short term rentals that are usually under a 3 weeks. Monthly rates for long term rentals going out for at least a month or more. The gray area where the sales people play is in that 3+ week range where they might choose weekly or they might go monthly. It’s all up to them in the moment.

I hope this helps. Tomorrow I’ll post my sample table that is producing the 7 day anomaly. I probably screwed something up in the cell references when copying the formula over.

 

[KRice]

OK, thanks...a lot to process here more carefully, but let me ask...the user inputs either a monthly rate or a weekly rate in the Unit Rate column? That's the starting point for any further rate breakdowns: if it's a monthly Unit Rate, then the monthly, weekly, and daily rates are factors of 1, 1/4, and 1/28 of that input monthly rate. And if the input Unit Rate is a weekly rate, there is no monthly component, and instead the weekly and daily rates are factors of 1 and 1/7 of that input weekly rate? Do I have that correct? This is what the sheet is set up to do, for the most part...other than the rounding...but I just want to confirm. By controlling the inputs this way, you might, and probably do, have weekly Unit Rates that are slightly more than 1/4 the monthly Unit Rate.