On Tuesday, July 30, 2013 6:03:51 AM UTC+2, Ben McClave wrote:
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 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 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 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