Trying to minimize a non-continuous function that depends on data-validation lists

I see two options.

The first one is a long-shot. But it might be possible to model it in such a way that Solver can be used. Based on your description of having string inputs, I doubt that this may be possible.

The other is brute-force VBA. Run though every option, record the parameters, record the results and report it with a pivot table.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I can mount a brute force attack on the problem. You will have to add another sheet with some tables that hold parameters but I'll tell you how to do that. Then you can "re-attach" the missing sheets.

Basically, I am going to loop through all possible settings (everywhere you have a drop down). However I need to know where to look for the "answer." Would that be cell P47? If you think it worthwhile I could capture the data in P25:P47.

You could compress the file and attach it as a ZIP file.

It probably doesn't matter. I just pried in under the hood and came up with the following: I have no idea how long it takes to do a calculation. There are 7 dropdowns for Storage with a combination of 7,200 ways. There are 4 for electrical with 40,000 ways. There are 3 for thermal with 16 ways.

Taken all together, there are 4,608,000,000 combinations.

Assuming one complete computation a second (very optimistic), that's 4,608,000,000 seconds or 76,800,000 minutes or 1,280 hours or 53,333 days or a tad over 146 years . I'm an old fart. I don't have 146 years.

So brute force is out. Here is a thought.

You have 24 different scenarios: 3 storage types with 2 temperature types and 2 thermal technologies, centralized of distributed and on demand or ASHP electric tanks. You will have to run a study for each of these.

All the rest of the dropdown are numeric data and could be modeled by Solver (theoretically).

Take "samples:" 3 low temp from (A), 3 low temp to (B), 3 high temp from (C) and so on.

Record these in columns A, B, C ... and in the last column the observed value for this combination. Then make assumptions for the values of A, B, C... Make another column that computes a predicted value = A*low-temp-from + B*low-temp-to + C*high-temp-from ...

Take the difference between the observed and predicted. Square it. Get the sum of the squares and minimize it using solver allowing solver to vary A, B, C ...

I can show you how to set this up with a small example.

Populating the variables (assuming one answer a second) would take about 2.5 hours per scenario. Figure on an 8 hour day at least.

Even at that, a linear model may not work. Some factors may be non-linear.

In theory: it's possible. In practice it's difficult and not really a task for a PC to tackle.

It is theoretically possible but rather intractable.

My apologies for the sampling explanation Basically it's s somewhat less than random trail and error. Record all the settings for a particular trial, and then record the results. Repeat this as many times as you can bear. I suggest holding two of the areas "constant" while you vary the parameters on the other. I wouldn't do every 5% but may take samples at 0%, 35%, 65% and 100%. Then switch what two you hold constant.

Then when you build up a database, you might be able to do some interpolation. Even if it is less than optimal. For example, you may know that the answer is somewhere between two values even if you don't know exactly what it is.

Attached is a very short Solver example.

I am making a BIG assumption here in that the equation that defines the results is A*(Low From) + B*(Low To) + C*(High From) + D*(High To). I dummied up the data not so randomly and this made the results come out too good.

Columns A:D is what got set. Column E is what was observed. Column F is the results using the coefficients, A, B, C and D. Initially I set these in the range K2:N2 as 1, -1, 1, -1 respectively. Obviously this produced different results in Column F than what is shown there now.

Column G is simply the difference between the observed and the predicted and it does not matter which order the subtraction goes since Column H is this value squared and will be a positive number unless for some reason you are using imaginary numbers.

Cell M4 contains the sum of the squares of the differences.

We now have all we need for Solver. We want to minimize cell M4 by varying the range K2:N2. Click the button on solver and you'll get these results. Since I picked my data poorly (not random enough), the coefficients yield exceptionally close results.

Now for some more bad news. I'm betting that the relationship is not linear like I assumed above. When in doubt, I go with a cubic (third order polynomial) equation. So it becomes:

A1*(Low From)^3+A2*(Low From)^2+A3*(Low From)+A4 + B1*(Low To)^3+B2*(Low To)^2+B3*(Low To)+B4 ... Oh yes, for every thing that has a numerical value and then for each scenario that doesn't have a numerical value.

And this will only get you the curve. To get the maximum or minimum, you'll need to use calculus to differentiate it and then then solve that equation for zero.

Yeah, right! In theory possible. In practice: Record what you set, record what you got as an answer for a number of combinations and make a best interpolation your eyeballs and brain can come up with. You may find some factors less important than others, so maximize them and hold them constant.

I'm sorry I can't give you a better answer, but when I first looked at this, I did not realize how many combinations there were and that a brute force method was impractical.