0

TL;DR: Want to recalculate a model based on random variables and take the result and put it in a table. What's best way to do that?

Longer Explanation: I have a model that is built upon multiple random variables. I am trying to understand the distribution of potential outcomes by recalculating that spreadsheet multiple times to see what the resulting value / outcome is. In the below picture you can see a super simple example. The Est Budget column is RANDBTWN(Min Budget, Max Budget). So, now how can we fill that table with all the simulations / outcome for the demand for that $5 widget

enter image description here

So far I have attempted to do this with data tables but I don't necessarily want to change certain inputs, just want to simulate with the existing assumptions. Really appreciate the help - thanks everyone.

Improve this question

1 Answer 1

Reset to default
0

The easiest way to do this without VBA is using a little hidden gem in Excel called "Data Table" found under the Data ribbon > What-if Analysis. Perhaps familiarise yourself with the Data Table feature using Excel help. You will only need a 1D table, but here is an example of a 2D variable table, of which the header column (x) and header row (y) are substituted into a formula, e.g. like this for calculation of a circle's radius:

enter image description here

In your case, you will simply substitute the iteration number into a cell. On each substitution, Excel will automatically recalculate the volatile RAND or RANDBETWEEN functions and the answer cell will be copied into the data table.

Let say your units sold is in F8. We will place the iteration number in C8. In A10 put the word "iteration" and in B10 put the formula =F8. In A11 to say A110 put the numbers 1 through 100. Select A10:B110, click Data tab, and click What-If Analysis > Data Table. Now put the reference C8 into the Column input cell leaving the row input cell textbox empty. Hit OK. Et voila!

Improve this answer
1
  • Doh! Only saw now you are indeed familiar with Data Tables from your last sentence below your picture. Data tables will work fine, even with a substitute into an unused, unreferenced cell, because of volatility. Certain Excel functions such as NOW() and RAND() are classified as volatile, meaning they must be recalculated on every workbook change, even if none of its parent cells changed. If you weren't using a volatile function you could in theory include the iteration cell in your formula = SUM(F3:F6) + C8*0
    – ExcelEverything
    Commented Mar 9, 2021 at 14:50

You must log in to answer this question.

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