Risk Asssesment Formulas

Options

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots for reference?

  • EWILSONNGB
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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) + .............................

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!