Have Excel automatically repeat a calculation for multiple cell values?

Factual Questions
1

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?

2

What are you looking for in the plot?

3

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:


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.

4

If you’re using the plot to find an optimum price, take a look at the solver.

5

Instead of just one “Price” cell, can you do a whole series of them (Price1,Price2,Price3,etc.)?

Let’s say Price=DemandAB*C

Instead, make it so that:
Price1=Demand+1ABC
Price2=Demand+2ABC
Price3=Demand+3AB*C
…

And so forth.

6

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.

7

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.

8

Bump - were you able to get this to work based on my little hint? :slight_smile: