Loop variables and keep the value that yields the max result

[Bearz]

Hello,

I have an Excel tool that I am using to model scenarios and I would like to automate part of it but am not sure how to write the code. I have a column of twelve cells (B2:B13), each of which is used to input variables that feed into a large series of calculations. I would like to cycle through each of these cells, one at a time, testing values from 0 to 100 in increments of 5 and keep the value that yields the highest calculated result in an adjacent cell (D2), then move on to the next cell.

I know this is possible, but I'm a bit of VBA novice and am having trouble piecing it together. Thanks in advance for your help!

[dflak]

We'd be glad to help you with this if you can provide a sample workbook with sample data.

Also you might consider either mathematical methods (like testing for first derivative = zero) or perhaps using Solver.

[Bearz]

Thanks for your offer of help. I've attached a very simplified example of what I'm trying to. Unfortunately, I can't attach the original model as it has some proprietary information in it, but hopefully this will demonstrate what I'm after.

Cells B2:B11 are the input cells for variable data. The cells in the adjacent column, C, calculate a number based on the input next to it. The specific formulas I entered in column C are pretty random - just formulas for which the ideal input would not be the same and do not reflect the calculations I'm using in my larger workbook. For each cell in in Column B2:B11, I would like the macro (or Solver) to run through each of the values in column G to determine which value results in the greatest calculated value in the adjacent cell in Column C and then leave that value in place.

I have tried running this calculation using Solver but have run in to a couple of issues:
1.) The background calculations are complex and running the simulation takes a very long time. However, I find that Solver spends a lot of the time refining to a level that is more precise than necessary when in actuality, searching in increments of 5% achieve a sufficient level of accuracy.
2.) The potential outcomes (in my real model) are very irregular and certainly non-linear. For example, if you were to graph the outcomes from all variables 0 - 100%, you might find there is a peak at 60%, but an even higher spike at 25%. I could be wrong here, but Solver seems to hone in on the peak that it is "closest" to and doesn't seem run through all the options.

I would be open to any solutions you have to offer. Thanks again for your help.

[Alf]

The background calculations are complex and running the simulation takes a very long time.

Have you tried to change/set it to a low value the "Max_Time(Seconds)" found under "Options" tab "All Methods"? Since you don't need an extreme accuracy you could test with 10 - 15 seconds and see if this gives you a sufficient accuracy.

but Solver seems to hone in on the peak that it is "closest" to and doesn't seem run through all the options

Yes that is correct, if you have a non-linear function with several optimal "peaks" solver will stop at the first one found based on solver's starting point.

Alf

[dflak]

Here is my crack at it. It uses helper columns and array formulas.

First we find the maximum overall dollar value: =MAX(C2:C11).
Then where it occurs: =MATCH(L2,C1:C11,0)
And the percent associated with it: =INDEX(B1:B11,L3,1)

Next we find the next lowest percent: =MAX(IF(B2:B11<L4,B2:B11,FALSE)) (array formula)
And where it occurs: =MATCH(L6,$B$1:$B$11,0)
And the dollar value associate with it: =INDEX($C$1:$C$11,L7,1)

Similarly we find the next highest percent:
=MIN(IF(B2:B11>L4,B2:B11,FALSE)) (array formula)
=MATCH(L10,B1:B11,0)
=INDEX($C$1:$C$11,L11,1)

And we “transcribe” them to the range L15:M17. We use this in the linest formula to get the coefficients of a quadratic equation: =LINEST($M$15:$M$17,$L$15:$L$17^{1,2}) (array formula). See note below when we are missing an end point.

Then we apply the equation to the percents (Column H).

We find the maximum dollar value in the 25% to 40% range (for this example): =MAX(IF(G2:G22>=L6,IF(G2:G22<=L10,H2:H22,FALSE))) (array formula)
And where it occurs: =MATCH(L23,H2:H22,0)
And the associated percentage: =INDEX(G2:G22,L24,1)

When the maximum value is on an end point, then there is no next lowest or next highest percent and some of the metrics evaluate to #N/A. When that happens, the values associated with the maximum values are assigned to the missing end point (Column N)

[Bearz]

dflak,

I really appreciate you taking a stab at this. Unfortunately, I don't think it gets to what I'm after, at least not in its present form. I perhaps should have been more clear about my example inputs: the percentages in column B were simply placeholder values; they do not necessarily represent the ideal input. By simply manually adjusting these values, you can see that the resulting values in column C change. The calculation you provided tells us that ideally the value in cell B10 should change from 30% to 35%, but if we enter 35% in that cell, the value in C10 actually decreases (for what its worth, it looks like 5% results in the greatest value in C10).

Some background which might provide further context: In my actual model, the input cells are used to guide decisions on a monthly level (in the sample calculation I provided there are 10 input cells - in my actual model there are 12) and each feeds in to a series of calculations related to that specific month. While there can be some carryover from month to month, the calculations are mostly independent of one another. This is why the formulas in column C of my Sample Model differ in each row: it represents my very quick attempt at simulating different ideal outcomes for each input.

What this means is that I need to test all 21 options (0 - 100 in increments of 5) in each of the input cells (B2:B11) because finding the input for cell B2 which yields the greatest value in cell C2 has little to no bearing on what the ideal input for cell B3 will be. Make sense?

I hope your effort at answering this didn't take up too much time. I do appreciate the help.

[Bearz]

Hi Alf,

To your comments:

"Have you tried to change/set it to a low value the "Max_Time(Seconds)" found under "Options" tab "All Methods"? Since you don't need an extreme accuracy you could test with 10 - 15 seconds and see if this gives you a sufficient accuracy."

Yes. Unfortunately, this hasn't consistently gotten me close enough, probably because of the second issue you commented upon...

"Yes that is correct, if you have a non-linear function with several optimal "peaks" solver will stop at the first one found based on solver's starting point."

... which is why I am looking for a method to cycle through a series of possible answers and then keep the best one.

Thansk

[Alf]

looking for a method to cycle through a series of possible answers and then keep the best one.

Not sure I understands that part correctly but perhaps a macro like this could be of help:

Please Login or Register  to view this content.

This will loop through all values B2 to B11 and change each of them in order to maximize the corresponding C value with the following constraint B value => 0 and B value <= 100 except for B10 where 0 will give a "#DIV/0!" error so min value of B10 => 5 %

In order to run the macro you must set a reference to Solver in Visual Basic. "Developers" tab, "Visual Basic" icon -> "Tools" -> "References" and tick box marked "Solver"

If you wish to do it step vise for each value from 0 to a 100 % in steps of five for all the B values in range B2:B11 then perhaps a macro running "Goal seek" would be a better choice.

Alf