locked
I need help with Custom Columns RRS feed

  • Question

  • I am building a SharePoint list based Risk Register and am working with a tricky Risk Matrix. The Risk matrix has a different measure only when it comes to 'Safety' risks (highlighted) and for other risks it is uniform.


    Depending on the selected likelihood and consequence Risk my [Risk Value] column populates with unique numbers.


    So, if  choice column [Risk Category] = " Safety''

    then

    [Risk] = IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low ")))

    and if my choice column 

    [Risk Category] = ''Integrity'' or ''Excellence'' or ''Reputation'' or ''Finance and resource allocation'' or ''Governance, legal, regulatory & compliance'' or ''Change'' or ''''Education and Student Experience'' or ''Research'' or ''Engagement''

    then

     [Risk]  =IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low ")))

    How do I combine these two statements to create a single custom formula ?

    I have tried =IF([Risk Category]="Safety",  IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low "))), IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low")))) 

    However, the second IF statement fails to take effect, all [Risk Category] choices return same value as for "Safety"

    I would greatly appreciate your help.


    Thursday, June 11, 2020 12:40 PM

All replies

  • I am building a SharePoint list based Risk Register and am working with a tricky Risk Matrix. The Risk matrix has a different measure only when it comes to 'Safety' risks (highlighted) and for other risks it is uniform.


    Depending on the selected likelihood and consequence Risk my [Risk Value] column populates with unique numbers.


    So, if  choice column [Risk Category] = " Safety''

    then

    [Risk] = IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low ")))

    and if my choice column 

    [Risk Category] = ''Integrity'' or ''Excellence'' or ''Reputation'' or ''Finance and resource allocation'' or ''Governance, legal, regulatory & compliance'' or ''Change'' or ''''Education and Student Experience'' or ''Research'' or ''Engagement''

    then

     [Risk]  =IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low ")))

    How do I combine these two statements to create a single custom formula ?

    I have tried =IF([Risk Category]="Safety",  IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low "))), IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low")))) 

    However, the second IF statement fails to take effect, all [Risk Category] choices return same value as for "Safety"

    I would greatly appreciate your help.


    Hello,

    From your description, the Risk matrix seems to have a same measure for the Risk Category. The formula to calculate the vlaue for other risks is the same as the "Safety". It is always

     IF([Risk Value]>=0.266,"Extreme",IF([Risk Value]>=0.171,"High",IF([Risk Value]>0.038,"Medium","Low ")))

     I see nothing changed.I guess you could make a mistake.

    That's why all [Risk Category] choices return same value as for "Safety"

    Best Regards,

    Michael Han


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, June 12, 2020 6:36 AM
  • Hello,

    Is there anything update?

    Please remember to mark the reply as an answer if it helps you.

    Best Regards,

    Michael Han

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, June 29, 2020 9:35 AM