Formula that identifies best rate and cost based on number of days

Hello All,

I'm looking for a formula that can help the user of the spreadsheet plug in the number of days a piece of equipment needs to be rented and determine the best cost. The issue is that there are 3 different rates: Daily, Weekly and Monthly. Attached is the spreadsheet. Is there anyway to do what I'm asking?

First, I'd like to thank you so much for your attempt to help me, as I'm in over my head.

Greater than or equal to. I think this formula is close but it doesn't take into consideration best rate possible. For example, if you need a piece of equipment for 4 days, it's cheaper to rent it for the week. Another example is if you need it lets say 7 days, you would be better off renting for 1 week rate and adding 2 daily rates. Does that make sense? Is what I'm asking even possible?

Thanks for trying again. Very close!!! The only thing the formula is not doing is taking into account a split situation. For example, if I need the equipment for 25 working days it should charge me one monthly rate and one weekly rate. And again, maybe what I'm asking for is not possible, I don't know.

Sorry, didn't work, but closer. If I put in 21 days its multiplying B2 by 2 and adding one day rate $152.03. Should be one month + one day ($89.68). I can't thank you enough for your efforts.

You got it!!! AWESOME WORK!!! Thank you soooo much for this! You ROCK!!

Who was that post directed to, Rick?

I hope it wasn't in reply to my post because I've been testing with various numbers of days and just found some wrong results

Might have to rethink this in the morning.

Ok, I've revised my suggestion, and I think this gives accurate results but this is only a quick fix.

In H2

=IF(MOD(B2,5)<=INT(E2/D2),IF(((MOD(B2,5)*D2)+((INT(MOD(B2,20)/5)+(MOD(B2,5)>INT(E2/D2)))*E2))<F2,MOD(B2,5),0),0)

In I2

=IF((INT(MOD(B2,20)/5)+(MOD(B2,5)>INT(E2/D2)))<=INT(F2/E2),IF(((INT(MOD(B2,20)/5)+(MOD(B2,5)>H2))*E2)<F2,INT(MOD(B2,20)/5)+(MOD(B2,5)>H2),0),0)

In J2

=INT(B2/20)+(((I2*5)+(H2))<MOD(B2,20))

I'll test it some more later and see if I can shorten the first 2 formula.

Jason,

It was directed to cplettner, sorry for the confusion. Thanks for your suggestions and hard work as well. It is much appreciated.

Originally Posted by madrick1991

Jason,

It was directed to cplettner, sorry for the confusion. Thanks for your suggestions and hard work as well. It is much appreciated.

No worries Rick, you're welcome.

I haven't looked into cplettner's formula in detail, but I would disagree with the results for 7 and 27 days, there may be other discrepancies that I haven't seen.

Jason,

You are correct, I did some quick testing yesterday and it looked right but as I started to work through it more it is not accurate in every situation. Thank you so much for bringing it to my attention. I'm still testing your suggestion but so far it looks good. Thank you again, for your assistance.

cplettner - Thank you as well for all your effort.

Jason,

Why can't I sum the cost column. The $s do not populate in the total field. Thanks

It's hard to say without seeing an updated sheet, if you're using the 4th formula from my original post to sum the cost, then it requires days on column B and quantity in column A, if either is empty then it will show $0

If you want the cost per unit in $ rather than the total cost for the quantity needed, then just delete *A2 from the cost formula.

I've done a bit more testing and refined some of the formula slightly, I've set it all up in the attached sheet.

Originally Posted by cplettner

My formula does indeed miscalculate on some "best price" values, which makes it entirely useless.

I wouldn't go that far, it only appears to be inaccurate if the number of days is 7, 27, 47, etc. So effectively it's 95% accurate

Probably just needs a little tweak somewhere, but I wasn't going to attempt to try and follow all of that rounding up and down