Excel spreadsheet question

June 23, 2005 9:08 AM

I have a complicated Excel valuation spreadsheet. The "bottom line" is affected by about 20 variables. I'm comfortable fixing about 17 of them, but for the remaining three, I wish to output to a 4x30 matrix, consisting of parameters A, B, and C, and the bottom line value that corresponds to each of the three. As it stands now, I can plug and chug, but it's a bit unwieldy, and I may wish to make a bigger matrix soon (I'll keep the 4 parameters, but I may wish to go 4x100 or the like). I can't reverse engineer the entire spreadsheet-- it would take forever. So, how do I build this matrix?

In other words. as it now stands, I fill the three cells manually and cut and paste the given "answer" to a hand-crafted matrix, but I want to be able to draw the matrix and have the spreadsheet put the answers for each set of parameters in the fourth column.

I realize that I may have phrased this question poorly. I'll try to respond to subsequent questions.

posted by Kwantsar to technology (6 comments total)

I'm no Excel guru, but I work in it quite a bit. That being said, I don't really understand your question too well. It could be that I'm just unfamiliar with the type of spreadsheet you are building. Is there any chance you could post a link to a screenshot?
posted by bwilms at 10:11 AM on June 23, 2005

I think I finally figured out what you mean. You essentially want to change A, B, and C 30 times and save all your outputs.

The easiest way to do this would be to get everything in your spreadsheet lined up in a row or column. Then you can just copy the row or column and change the variables. The final outputs will be right next to each other (or on top of each other).

The other way is to make multiple sheets and then have a recapitulation sheet with your 4x30 matrix or whatever. I don't suggest this because it doesn't scale.
posted by grouse at 10:26 AM on June 23, 2005

The other way involves Visual Basic. I only know this second hand - someone I know has done exactly this thing. I'll try to have him explain it.
posted by dpx.mfx at 10:37 AM on June 23, 2005

Excel's scenario analysis tools do what you want. In Excel 2000 (the latest version I have) you'll find them under Tools/Scenarios...

You need to set up and name one scenario for each of your parameter combinations. Then you can have Excel run a summary report listing the scenarios side-by-side or to a pivot table. Microsoft does a much better job of explaining this that I could hope to:

Some Microsoft help pages on Scenario Analysis in Excel.
posted by Opposite George at 10:51 AM on June 23, 2005

John Walker of J-Walk Blog may be able to help. He's written how-to books on Excel.
posted by Doohickie at 1:39 PM on June 23, 2005

Opposite George takes the prize. The "scenarios" tool is what I was looking for. It's very unwieldy, with a wizard-style interface; I wish I could have simply copied from a spreadsheet without having 20 keystrokes per scenario.

Nonetheless, it was a workable, semi-permanent solution. For this I am grateful.
posted by Kwantsar at 2:31 PM on June 23, 2005