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]

I see. For arguments sake, if I wanted it to display MWD regardless of the option selected (ie, Custom, Weeks, Days), I would just add those to the OR statement?
(OR(I18=“Months”,I18=“Weeks”,I18=“Days”,I18=“Custom”),…

 

[KRice]

if I wanted it to display MWD regardless of the option selected (ie, Custom, Weeks, Days), I would just add those to the OR statement?

Yes...you could, but if that were the case, then an OR statement is not needed. If any selection (Custom, Months, etc.) should result in the number of months displaying, without exception, then

=IF(ISBLANK(C18),"",DATEDIF($A$21,$A$23+1,"M"))

But there was a reason for using this logic: if "Weeks" or "Days" are selected, there was previously a desire expressed to not show months and to report in terms of WD or D.