0

I have a very complex financial model made up whose output changes based on the 2 inputs. I want to make a table where the column has all possible values of input 1 and row has all possible value of input 2. Each cell in the table should have the output corresponding to the two inputs. How do I go about this?

Can you provide a link or a function that I can use to do this?

3
  • 1
    If you can write a formula using Excel's notation for your financial model, this is straightforward. Enter the values for input 1 in column A, starting with A2. Enter the values for input 2 in row 1, starting with B1. In cell B2, enter the formula for your model, but replace every instance of input 1 with "$A2" (don't type the quotes) and every instance of input 2 with "B$1" (again, no quotes). Fill the formula down to the last value in column A, and then fill the entire selection (B2:Bn) to the right until you reach the last value in row 1. Jul 18, 2017 at 3:28
  • Thanks. However, the model is too complex to be put in one formula. Hence, I need a function which populate the values based on the two inputs. Jul 18, 2017 at 6:07
  • You'll have to provide much more detail about your problem. How is the result calculated, are there simultaneous equations that need to be solved? Jul 18, 2017 at 13:43

1 Answer 1

0

Write a VBA script/macro to iterate through the cells in your table, pasting the grid coordinates into your input value cells, recalculate, then copy the output the grid location. Given your 2-dimensional grid you should only require to FOR loops.

Something like this

FOR i to <Input_1_Max> 
  FOR j to <Input_2_Max> 
     Range(Input_1) = i
     Range(Input_2) = j
     Application.Calculate
     Cells(i,j).value = Range(Output).value
   Next j
Next i

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .