# Have Excel automatically repeat a calculation for multiple cell values? **URL:** \ **Category:** Factual Questions\ **Created:** [October 4, 2011, 2:25am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511 "2011-10-04T02:25:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Absolute](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Absolute](https://boards.straightdope.com/u/Absolute)\ **Post date:** [October 4, 2011, 2:25am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/1 "2011-10-04T02:25:38Z")
I have a large, complicated Excel spreadsheet. It’s an economic model of sorts that is designed for people to easily review, so it contains about twenty different sheets with a wide variety of parameters, expansions, and tables, all calculated automatically from a sheet where you enter about 40 parameters to configure the model. The output of the model can be summarized as just one number, we’ll call it “Price”. I would like to be able to plot this model output (Price) as a function of a single input parameter (call it “Demand”) automatically. Currently I have to create a separate spreadsheet (Demand vs. Price), manually enter each input Demand value into the parameters sheet, and manually record the corresponding Price output into the separate spreadsheet, then plot it. Is there any way to do this automatically in Excel? ---
**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\ **Post date:** [October 4, 2011, 2:37am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/2 "2011-10-04T02:37:51Z")
What are you looking for in the plot? ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [October 4, 2011, 2:52am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/3 "2011-10-04T02:52:33Z")
One thing I think might help - Excel VBA could definitely automate the population of your demand versus price spreadsheet. You can set up a loop, tell it at what demand intervals you want to test, and have it record all the values for you. This is a sample of what it might look like, as nearly as I can figure out at this point in the evening: ```auto dim row as integer, price as double, demand as double for row = 1 to 20 price = row * 5 ParametersSheet.Cells(2, 4).value = price Application.Calculate demand = outputsheet.cells(5, 6).value PlotterSheet.cells(row + 1, 2) = price PlotterSheet.cells(row + 1, 3) = demand next ``` That would test price values from 5 to 100, at intervals of five. It assumes that price has to be written into row 2, column 4 of ParametersSheet, and the demand read out of row 5, column 6 of outputsheet. Then the values are written into columns 2 and 3 of PlotterSheet The Application.Calculate statement makes sure that all of the calculations in your model are updated for the new price value. I hope that this is a useful place to start. ---
**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\ **Post date:** [October 4, 2011, 3:42am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/4 "2011-10-04T03:42:42Z")
If you’re using the plot to find an optimum price, take a look at the [solver](http://office.microsoft.com/en-us/excel-help/introduction-to-optimization-with-the-excel-solver-tool-HA001124595.aspx?CTT=3). ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [October 4, 2011, 4:31am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/5 "2011-10-04T04:31:29Z")
Instead of just one “Price” cell, can you do a whole series of them (Price1,Price2,Price3,etc.)? Let’s say Price=Demand_A_B\*C Instead, make it so that: Price1=Demand+1_A_B_C Price2=Demand+2_A_B_C Price3=Demand+3_A_B\*C … And so forth. ---
**Author:** ![Absolute](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@Absolute](https://boards.straightdope.com/u/Absolute)\ **Post date:** [October 5, 2011, 1:15am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/6 "2011-10-05T01:15:24Z")
> [@Reply](#): > > Instead of just one “Price” cell, can you do a whole series of them (Price1,Price2,Price3,etc.)? > > Let’s say Price=Demand_A_B\*C > > Instead, make it so that: > Price1=Demand+1_A_B_C > Price2=Demand+2_A_B_C > Price3=Demand+3_A_B\*C > … > > And so forth. The calculation is much too complicated for this, that’s the whole problem. I think **chrisk** ’s solution is the best, I’ll try it out tonight, thanks. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 5, 2011, 3:32am UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/7 "2011-10-05T03:32:54Z")
> [@Absolute](#): > > The calculation is much too complicated for this, that’s the whole problem. I think **chrisk** ’s solution is the best, I’ll try it out tonight, thanks. I have done this kind of thing and **chrisk** ’s solution is the \*only \*solution. For example, Monte Carlo modeling of project schedules. Solver is good when you need to converge on a single value, but not if you want to capture a large range of a function. ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [October 6, 2011, 4:20pm UTC](https://boards.straightdope.com/t/have-excel-automatically-repeat-a-calculation-for-multiple-cell-values/598511/8 "2011-10-06T16:20:53Z")
Bump - were you able to get this to work based on my little hint? 🙂