Solver Question: Optimization based on list of values?

[poleandreel]

Hi Guys,

I am trying to find the optimal solution to a problem based on a set of values. For example, if I am trying to decide which 2 houses to buy and I have the cost of each house and the number of amenities (such as bedrooms, bathrooms, lot size etc)....

is it possible to have solver determine which set of houses from the list to buy if I have a maximum budget and want to have the most number of bedrooms, bathrooms, lot size, pool size, etc.

I know solver can find an optimal price to pay based on inputs but can it give me the exact value of the houses (from the list) and then i will have the maximum of each amenities?

Thanks and sorry if I'm confusing.

[outofthehat]

The hard bit of this question is, how do you calculate the value of, e.g. a pool? Solver can't help you unless you create the logical rules that create a 'value' statistic for each house based on beds, pool etc - and that depends on your preferences. Once you have the 'value' statistic for each house, create two input cells in which you'll input HOUSEID (unique ID for each house - first column of your data), then a cell with
=VLOOKUP(<1st input cell>,<housedata>,<column for value statistic>,FALSE)+VLOOKUP(<2nd input cell>,<housedata>,<column for value statistic>,FALSE)-IF(<1st input cell>=<2nd input cell>,1000000,0)
- this calculates the 'value' of the two chosen houses, but creates a big negative value if the two cells are the same (you'll need that to exclude the possibility of solver picking the same house twice)

You'll also need a constraint cell
=VLOOKUP(<1st input cell>,<housedata>,<column for price>,FALSE)+VLOOKUP(<2nd input cell>,<housedata>,<column for price>,FALSE)
You can the use solver to maximise the 'value' by changing the two input cells, with the constraint that 'constraint cell'<=your budget