Find Cheapest Price Combination

Hi! Again! You guys are so helpful I have to ask this question because I know you will be able to help me!

I have three rental prices. a 1 day rental costs 700
a 1 week rental costs 1900
a 4 week rental costs 5500

I have a certain number of days ill need the rental which varies per project.
I would LOVE for excel to make sure i have the correct number of days required covered at the cheapest cost.

at first I was using if the number of days is less than 3 then use (2) "day" rentals since if i had 3 days it would be cheaper to just buy a week. and that worked but then when i got to one week my formula was waaay to long and not even correct.

the only catch (and im not sure that it is) is that we can only use the rental for the 5 work days in a week.

I hope this is enough information if anyone could give me a place to start, or link to another page that would be able to help me, it would be great!

Based on the 'catch' does that mean that 4 week rental would be entered as 20 days, or do you enter start and end dates?

A sample sheet showing how you enter your data, and how you want the results would be helpful.

@jason.b75 yes i suppose it would work better to enter it as 20 days to not have to convert it to weeks etc.

@thecuteone: that works, but if i have need more than 4 weeks id like it to spit out the price for (2) 4 week rentals, or (1) 4 week rental and (1) 1 week rental, or whatever the need is. @jason im working on getting a sheet


its cheaper to use (2) 1 week rentals-->3800
rather than (1) 4 week rental--> 5500 and i cant get it recognize that

I just cut the info from my master sheet into this sample. Hope it gives some type of visual as to what id like to do

I don't think you will find an easy way to do this with a single formula, there is a table in sheet 2 used to provide the correct combinations.

sample3(1).xlsx

Thanks Jason, that will work just fine and its probably the easiest since we dont ever have an need over around 30 days.

Unless there are different rates for longer periods then that will work for an unlimited number of days anyway.

The pattern remains consistant so the formula uses multipliers to calculate beyond the 20 days in the table.

I tried with random values up to 500 for number of days and it works fine.

Jason, I JUST realized what you meant with unlimited number of days. Im trying to paste it into my master workbook to make it work and what is table 1? How would I define it? I think thats the only thing Im missing

Table1 is the table in sheet 2 of the sample sheet, to define it, copy the original to your master workbook, then with the table, or any cell in the table selected, press Ctrl t, then enter.