Risk Asssesment Formulas

EWILSONNGB

I am setting up a risk assessment form which information is collated into a seperate sheet. On the sheet i need to be able to convert the answers into numbers and then calculate to provide a Net Worth.

There are over 100 controls which will all have a seperate number attached to them which will be part of the calculation. Can you advise on which formula is best to use instead of doing one formula for each Control?

thank you

Paul Newcome

Are you able to provide some screenshots for reference?

EWILSONNGB

yes -

We have a formula which pulls through the chosen control answers from the risk assessment form to the risk assessment sheet. Which is stored in Controls Chosen column.

To start with we are only using 3 controls and each control will have its own weight number.

From the Controls chosen column i have created 3 more columns, one for each control. if a control was chosen it will show up with their weight number in the correct column.

ICT policy = 1

ISP = 2

BCP = 3

my question is, when we have 100+ controls, will i need 100 columns to continue with my risk assessment or is there a way to get all this information in one column/formulas?

thank you

Paul Newcome

My next questions would be...

Are the options always the same across all controls (Very High, High, etc.), and are the weights always the same (Very High always equals 5, High always equals 4, so on and so forth)?

If the answer to both questions is "Yes", then you are in luck. Basically you would count how many across the row are "Very High" and multiple that result by 5. Then you would count how many are "High" and multiply that result by 4. Keep doing that for the other options and then add them all together to get the weighted score for the entire row.

=(COUNTIFS([First Column]@row:[Last Column]@row, @cell = "Very High") * 5) + (COUNTIFS([First Column]@row:[Last Column]@row, @cell = "High") * 4) + .............................