Sensitivity Analysis help

[AlexPanebianco]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID

[/TD]
[TD]Score1
[/TD]
[TD]Score2
[/TD]
[TD]Score3
[/TD]
[TD]Score4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
Hi all,

I'm attempting to do some sensitivity analysis on a model I've built.


My data is set out as in the above table, with scores being either 1, 2, 3 or 4.
The scores are then weighted, and a total score is provided for each ID, from which I can calculate an average score.
I'm looking to now identify how the average score changes when any given column is set all to 1 or 4 (min and max scores).

So I'm looking for a procedure which basically does the following:

Set all scores in Score1 column to 1 - Record the result
Reset scores to original
Set all scores in Score1 column to 4 - Record the result
Reset scores to original
Set all scores in Score2 column to 1 - Record the result
and so on and so forth.



For the time being, I'm relying on the very tedious method of setting up a What-If Scenario for every event (which, in my full data, works out to be almost 50 scenarios).

For this, I've created a new, blank column above all the data and the formulae in each scoring cell now also contains a new part which asks the formula to first look at the blank row to check it's empty. If the sensitivity cell above the column isn't empty, the whole column is replaced with whatever's in the sensitivity cell.
The What-If is then set up to say "What result do we get when that sensitivity cell is 1 or 4?"

Is there a better way to do this? My experience with VBA is very limited, but I know how to roughly set up a macro if given the code, so this is an option, just, unfortunately, not something I'm capable of creating myself just yet.