VBA to run all variable combinations in a calculation model and store result

[yves.mosselmans]

Hi,

I received a workbook with a calculation model which calculates a price based on 6 different criteria and complex formulas. There are over 40,000 possible combinations, choosing different criteria.

I managed to create a table with all possible combinations. Criteria 1 is stored in Column A, Criteria 2 is stored in Column B, etc up to 6 columns. I can't use a formula for this.


Would it be possible to create a vba code to run all combinations in the calculation model and store the result in the last column?


A very simplified example could be (using 2 variables in 2 columns):

Col 1 (Month) Col 2 (Rateplan)
January Gold
February Silver
March
April
....

The calculation model could be
Month (data validation from Col 1)
Rateplan (data validation from Col 2)
Charge = formula that calculates the charge based on two above criteria


In this example a Combination could be January and Gold... I would like the macro to run this (and all other) combination in the calculation model and store the outcome as a value in Column 3 (next to the combination).


Could a vba wizzard help me with this?

 

[Auric__]

I received a workbook with a calculation model which calculates a price
based on 6 different criteria and complex formulas. There are over
40,000 possible combinations, choosing different criteria.

I managed to create a table with all possible combinations. Criteria 1
is stored in Column A, Criteria 2 is stored in Column B, etc up to 6
columns. I can't use a formula for this.


Would it be possible to create a vba code to run all combinations in the
calculation model and store the result in the last column?


A very simplified example could be (using 2 variables in 2 columns):

Col 1 (Month) Col 2 (Rateplan)
January Gold
February Silver
March
April
...

The calculation model could be
Month (data validation from Col 1)
Rateplan (data validation from Col 2)
Charge = formula that calculates the charge based on two above criteria


In this example a Combination could be January and Gold... I would like
the macro to run this (and all other) combination in the calculation
model and store the outcome as a value in Column 3 (next to the
combination).


Could a vba wizzard help me with this?

Not without more information.

With your table, I'm assuming it lists each criterion once, right? (Meaning,
for example, January thru Dcember listed once, not 40,000 times.)

Also, what is the final output in the 7th column supposed to look like? Is it
supposed to be literally "January Gold etc." or something else?

 

[yves.mosselmans]

My table lists all possible combinations of the variables. In the small example it would be

Col 1 Col 2
January Gold
January Silver
February Gold
February Silver
March Gold
March Silver
Etc.

Final outcome in the last column would be the result of the calculation. The code should loop through all combinations in each row, take the values and replace the variables in the calculation module which calculates the charge with a formula. This result should be then stored in the last column of the row to get:

Col 1 Col 2 Col 3
January Gold Calculation macro
January Silver Calculation macro
February Gold Calculation macro
February Silver Calculation macro
March Gold Calculation macro
March Silver Calculation macro

 

[GS]

My table lists all possible combinations of the variables. In the

small example it would be

Col 1 Col 2
January Gold
January Silver
February Gold
February Silver
March Gold
March Silver
Etc.

Final outcome in the last column would be the result of the
calculation. The code should loop through all combinations in each
row, take the values and replace the variables in the calculation
module which calculates the charge with a formula. This result should
be then stored in the last column of the row to get:

Col 1 Col 2 Col 3
January Gold Calculation macro
January Silver Calculation macro
February Gold Calculation macro
February Silver Calculation macro
March Gold Calculation macro
March Silver Calculation macro

Again.., not enough info. Are we supposed to guess what the other
criteria are AS WELL AS guess what the logic is for the calcs? What
operators get used on which criteria, and what is their position in the
calc formula?

I'm fairly certain that nobody here does magic so give up the info we
need to help you OR just go away!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

"I can't use a formula for this."

Why not? Looks like a perfect candidate for worksheet formulas to me
(given your explanation)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ben McClave]

Hello,

I think you could do this without macros (although it will probably take a long time to run with 40K combinations) by using Data Tables.

Here are the steps I took to recreate your data and generate a list of all possible outcomes for each combination. If you try this, experiment with a small sample to see how long it takes to calculate before running a large population.

1. On a new worksheet, copy your table of parameter combinations to cells A1:F400001 (or however many rows you actually have).

2. Beginning in cell H1, enter these formulas:
H1: =INDIRECT("R" & $H$4 &"C1", 0)
I1: =INDIRECT("R" & $H$4 &"C2", 0)
J1: =INDIRECT("R" & $H$4 &"C3", 0)
K1: =INDIRECT("R" & $H$4 &"C4", 0)
L1: =INDIRECT("R" & $H$4 &"C5", 0)
M1: =INDIRECT("R" & $H$4 &"C6", 0)

3. In cell H4 enter the number 2 (assumes data in your parameter table begins in row 2)

4. Enter numbers 2, 3, 4, 5, 6, & 7 in cells H5:H10.

5. In your workbook, replace any parameter references with references to the cells in the range H1:M1. The end result of this change will be that allof your parameters can be found in cells H1:M1 and any changes to that range will ripple through the model (test this by changing the value in cell H4).

6. In cell I4 enter a formula referencing your desired result calculation (for example, if the workbook calculates an NPV in Sheet!A1, then the formula in cell I4 would be =Sheet!A1). You can include references to several output cells by using other cells in row 4 (I4, J4, K4, L4 etc.).

7. Highlight all cells from H4:I10 and go to DATA > WHAT IF ANALYSIS > DATATABLE

8. In the "Column Input Cell" box enter the formula: =$H$4 and click OK

The result should be that the row number in cell H4 will feed the indirect formulas in cells H1:M1, which will feed the corresponding parameters to your model. The formula in cell I4 will pull in the calculation(s) of interest and the resulting table will list these results for each row you specify..

If the workbook becomes slow due to the Data Table, you can set the calculation mode to either "Manual" or "Automatic Except for Data Tables".

Good Luck,

Ben

 

[yves.mosselmans]

Hello,



I think you could do this without macros (although it will probably take a long time to run with 40K combinations) by using Data Tables.



Here are the steps I took to recreate your data and generate a list of all possible outcomes for each combination. If you try this, experiment with a small sample to see how long it takes to calculate before running a largepopulation.



1. On a new worksheet, copy your table of parameter combinations to cellsA1:F400001 (or however many rows you actually have).



2. Beginning in cell H1, enter these formulas:

H1: =INDIRECT("R" & $H$4 &"C1", 0)

I1: =INDIRECT("R" & $H$4 &"C2", 0)

J1: =INDIRECT("R" & $H$4 &"C3", 0)

K1: =INDIRECT("R" & $H$4 &"C4", 0)

L1: =INDIRECT("R" & $H$4 &"C5", 0)

M1: =INDIRECT("R" & $H$4 &"C6", 0)



3. In cell H4 enter the number 2 (assumes data in your parameter table begins in row 2)



4. Enter numbers 2, 3, 4, 5, 6, & 7 in cells H5:H10.



5. In your workbook, replace any parameter references with references to the cells in the range H1:M1. The end result of this change will be that all of your parameters can be found in cells H1:M1 and any changes to that range will ripple through the model (test this by changing the value in cellH4).



6. In cell I4 enter a formula referencing your desired result calculation(for example, if the workbook calculates an NPV in Sheet!A1, then the formula in cell I4 would be =Sheet!A1). You can include references to several output cells by using other cells in row 4 (I4, J4, K4, L4 etc.).



7. Highlight all cells from H4:I10 and go to DATA > WHAT IF ANALYSIS > DATA TABLE



8. In the "Column Input Cell" box enter the formula: =$H$4 and click OK



The result should be that the row number in cell H4 will feed the indirect formulas in cells H1:M1, which will feed the corresponding parameters to your model. The formula in cell I4 will pull in the calculation(s) of interest and the resulting table will list these results for each row you specify.



If the workbook becomes slow due to the Data Table, you can set the calculation mode to either "Manual" or "Automatic Except for Data Tables".



Good Luck,



Ben

Thanks Ben, much appreciated! I have tried it on a small model and it works. I will now give it a try on the larger model (maybe on first thousand rows) which contains more complex formulas.

Yves