# More Refined Method

#### Jon_AK

##### New Member
Am having a bit of a time creating a non-VBA method with this particular problem: When equipment is rented, it is rented by the day, 5 day week or 20 day month. The break point is 1 or 2 days for day rate, 3 - 10 consecutive days for weekly rate & greater than 10 consecutive days for monthly rate. The complication comes when having to accomodate extra days at day rate when either the weekly rate or monthly rate is in effect and/or accomodate extra days or extra weeks when the monthly rate is in effect. Currently I have a way long set of nested if-else statements using MOD(), INT(), ROUND() functions which only works part way. Example, rental for 27 days equals one 20 day month, one 5 day week and 2 days. Cannot seem to get it together so that the formula for this scenerio will work for any given amount of rental days.

#### p45cal

##### Well-Known Member
27 days equals one 20 day month, one 5 day week and 2 days
Why don't the remaining 7 days become part of '3 - 10 consecutive days for weekly rate'?

#### Jon_AK

##### New Member
Why don't the remaining 7 days become part of '3 - 10 consecutive days for weekly rate'?
Mainly because the first 5 days after the month rate are billed as 1 week and the remaining 2 days are a day rate. That is how the billing is received and when bidding a job, every dollar can make a real difference in being awarded the job or losing it by small dollar amounts.

#### p45cal

##### Well-Known Member
…so 7 days by themselves would be billed as a 5 day week + 2 days' day rate? ignoring the 3 - 10 consecutive days for weekly rate.

#### Jon_AK

##### New Member
…so 7 days by themselves would be billed as a 5 day week + 2 days' day rate?
Exactly...

#### p45cal

##### Well-Known Member
…ignoring the 3 - 10 consecutive days for weekly rate.?!

#### Jon_AK

##### New Member
…ignoring the 3 - 10 consecutive days for weekly rate.?!
Yes. The trick with this particular example is when bidding the job, the job requires a total of 7 days work. The daily rate is \$100 & the weekly rate is \$375 so the amount charged would be \$375 + \$200 = \$575. If left at a weekly rate for all 7 days, then the total runs up to \$750. Makes a huge difference when dealing with expensive rental equipment.

#### p45cal

##### Well-Known Member
so the 3-10 days is never used anywhere, at all, ever.

#### Jon_AK

##### New Member
so the 3-10 days is never used anywhere, at all, ever.
No. There are always times when the total amount of work days is greater than 2 days, greater than 5 but less than 10 etc. The weekly & monthly rates are for consecutive work days. i.e. 1 - 10 consecutive days for weekly rate and 1 - 20 consecutive days for monthly. Having to combine the weekly rate with daily rate or monthly with weekly & daily rates are a regular occurence. This is why the breaking points are set as they are. 1 or 2 days = day rate, 3 - 10 days = weekly rate and 11 - 20 days = monthly rate.

#### p45cal

##### Well-Known Member
consecutive work days
So I don't make mistakes:
Assume non-working days are Sat and Sun.
If I hire something on Wed am and pay for 5 days, when do I hand it back?
Then depending on your answer, how am I billed: I weekly rate of \$375? or something else?

#### p45cal

##### Well-Known Member
FYI, there is a neat formula, I'll have to dig it out, for calculating the likes of income tax where people pay in bands, to see if I can press it into this scenario.
Otherwise, what version of Excel do you have? Does it support the LET and LAMBDA worksheet functions.
Finally, if push comes to shove, would a user-defined function be acceptable (uses vba (macro code))?

#### Jon_AK

##### New Member
Assume non-working days are Sat and Sun.
If I hire something on Wed am and pay for 5 days, when do I hand it back?
Then depending on your answer, how am I billed: I weekly rate of \$375? or something else?
Day of week doesn't matter for this. weekly rate runs for 7 days but workers only work 5 of those days regardless of when they start their week so, with that said, the equipment would be due back in on the following tuesday. It would be billed at the weekly rate of \$375

#### Jon_AK

##### New Member
FYI, there is a neat formula, I'll have to dig it out, for calculating the likes of income tax where people pay in bands, to see if I can press it into this scenario.
Thank you for all your help. I thought about income taxes & how the different brackets work but couldn't seem to apply that to this. Probably some dense part of my brain.

Otherwise, what version of Excel do you have? Does it support the LET and LAMBDA worksheet functions.
Finally, if push comes to shove, would a user-defined function be acceptable (uses vba (macro code))?
Current version is Microsoft 365 2212 Build 15928.20216. As for VBA, and it does appear to support those two functions. I would prefer to keep VBA out but if it is the only possible way, I'll certainly take a look.

#### p45cal

##### Well-Known Member
Current version is Microsoft 365 2212 Build 15928.20216.
Can you tell me if that means LET and LAMBDA are available to you in a cell formula?

#### Jon_AK

##### New Member
yes, LET & LAMDA are both available for use.

#### p45cal

##### Well-Known Member
Like you, I'd want this to be an elegant and the shortest possible formula. If I can do it it may not happen quickly(!)
So:
1. Could you attach a workbook with a column with say 1 to 50 representing days hire
2. A manually (or otherwise) calculated set of expected results in another column (adding intermediate calculation columns if you want) so that I can compare/test my results.
3. If you want to do this for more than one hire item, include a table of a few items with 20, 5 and day, hire rates.

As an aside, using your rates in meg #7 (what's the 20-day rate btw?), if I was wanting to hire from you an item for 4 days, it would be cheaper for me to hire from you for 5 days; 4 days at \$100 (=\$400) against 5 days at \$375. So your equipment would be lying idle for a day on my site, with you unable to hire it to anyone else?

I would expect not to use vba since you have LET and LAMBDA.

Last edited:

#### Jon_AK

##### New Member
Like you, I'd want this to be an elegant and the shortest possible formula. If I can do it it may not happen quickly(!)
I can follow through with your request but will need a few hours due to appointments occuring shortly. Will post back later today.

#### Jon_AK

##### New Member
As an aside, using your rates in meg #7 (what's the 20-day rate btw?), if I was wanting to hire from you an item for 4 days, it would be cheaper for me to hire from you for 5 days; 4 days at \$100 (=\$400) against 5 days at \$375. So your equipment would be lying idle for a day on my site, with you unable to hire it to anyone else?
First off, we do not rent to others, we have to rent from a rental company for the jobs we do. Second, all of this pricing method is used for bidding work. If I bid for workers to use equipment for 8 days, I will use the weekly rate as it is cheaper to rent for 2 weeks than 1 week and 3 days. I know it may sound backwards but this is the rental company pricing schedules. As for getting you a worksheet, I have one created that is similar to what I use, but looking for the method to send it to you

#### p45cal

##### Well-Known Member
In the attached (adapted file that you sent me in a Private Conversation):
Formulae in cells J5:J13. Each formula spills one column to the right.
The formula is supposed to look at all prices from the required no. of days to 20 + the required no. of days and find the minimum price among them. The spilled cell to the right shows actual no. of days you should use to get that price.
A pleasant surprise in cell J10 showing that 3 weeks rental is cheaper than your expected 1 month rental and…
in J7 a 1 month rental is cheaper than 2 weeks + 2 days.

Columns P:Q show lambda derivation. You can delete these.
The formula is longer than it need be; work in progress.

#### Attachments

• 24.9 KB Views: 1

#### Jon_AK

##### New Member
In the attached (adapted file that you sent me in a Private Conversation):
Formulae in cells J5:J13. Each formula spills one column to the right.
The formula is supposed to look at all prices from the required no. of days to 20 + the required no. of days and find the minimum price among them. The spilled cell to the right shows actual no. of days you should use to get that price.
A pleasant surprise in cell J10 showing that 3 weeks rental is cheaper than your expected 1 month rental and…
in J7 a 1 month rental is cheaper than 2 weeks + 2 days.

Columns P:Q show lambda derivation. You can delete these.
The formula is longer than it need be; work in progress.
Columns P:Q show lambda derivation. You can delete these.
The formula is longer than it need be; work in progress.
Looks good what you've done, I would have spent the next month to get this In column P, is this where you defined the Name "RentalCost" and that you're still whittling it down?

#### p45cal

##### Well-Known Member
In column P, is this where you defined the Name "RentalCost"
No, it's where I played around developing it. I then copied first part of the Lambda formula (before the arguments in parentheses after it) to a Name RentalCost, in the Name Manager. Column P should be deleted.
and that you're still whittling it down?
I could whittle it down, subject to your successful extensive checking/testing of it.

#### Jon_AK

##### New Member
it all looks good right now and will keep you posted

#### Jon_AK

##### New Member
I could whittle it down, subject to your successful extensive checking/testing of it.
About the only thing thus far that would be nice to be corrected is how the formula spills over the duration of days into the adjacent column.

#### p45cal

##### Well-Known Member
I thought that value would be useful instead of having manually to work out how many days' rental are needed to arrive at that price. Sometimes, as we've already seen, it's not the obvious one.
how the formula spills over
by which you mean you don't want it at all?

#### Jon_AK

##### New Member
The spill over value was useful as it verified the duration of what was manually entered but, the duration for the equipment rental is supplied by the labor section of the workbook and not vice versa. The combination of the labor days to complete the project combined with the derived cost of the rented equipment based upon the labor days serves as a cost analysis once the job has been completed. So yes, please if you will, remove the spill over.