Sensitivity Analysis help

AlexPanebianco

New Member
Joined
Oct 30, 2018
Messages
1
ID

Score1
Score2
Score3
Score4
A
1
1
2
1
B
1
3
2
4

<tbody>
</tbody>
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,099,455
Messages
5,468,759
Members
406,605
Latest member
MikeGazzy

This Week's Hot Topics

Top