locked
Sharepoint 2013 Multiple Responses in a calculated column RRS feed

  • Question

  • Hi,

    I am currently trying to create a calculated column based on a risk register, where there is a drop down column for a user to select the likelihood and another for them to select consequence. The calculated column will then look up both other responses to come up with a risk level.

    I have tried all the mentioned workabouts for the "in excess of 7 nested IF functions, but have had no luck, then thought perhaps if I created a calculated column to concatenate both the likelihood and risk and then use the new risk calculated column to work of the concatenate column there would be less IF functions available, as the final risk scoring is based on only 4 outputs - Extreme, High, Moderate, Low.

    So I came up with the below which works in excel - but keep getting errors in sharepoint.

    If anyone has any ideas - it would be greatly appreciated.Many thanks,

    Jo

    =IF(OR([COMBINED CALC]="Almost CertainModerate (Medium)",[COMBINED CALC]="Almost CertainMajor",[COMBINED CALC]="Almost CertainCatastrophic",[COMBINED CALC]="LikelyMajor",[COMBINED CALC]="LikelyCatastrophic",[COMBINED CALC]="PossibleMajor",[COMBINED CALC]="Possiblecatastrophic"),"Extreme",IF(OR([COMBINED CALC]="Almost CertainInsignificant",[COMBINED CALC]="Almost CertainMinor",[COMBINED CALC]="LikelyMinor",[COMBINED CALC]="LikelyModerate (Medium)",[COMBINED CALC]="PossibleModerate (Medium)",[COMBINED CALC]="UnlikelyMajor",[COMBINED CALC]="UnlikelyCatastrophic",[COMBINED CALC]="raremajor",[COMBINED CALC]="rarecatastrophic"),"High",IF(OR([COMBINED CALC]="PossibleInsignificant",[COMBINED CALC]="unlikelyInsignificant",[COMBINED CALC]="unlikelymajor",[COMBINED CALC]="rareinsignificant",[COMBINED CALC]="rareminor"),"Low",IF(OR([COMBINED CALC]="likelyinsignificant",[COMBINED CALC]="possibleminor",[COMBINED CALC]="unlikelymoderate (medium)",[COMBINED CALC]="raremoderate (medium)"),"moderate",""))))

    Friday, December 2, 2016 1:44 AM

All replies

  • Hi

    based on your local culture settings, try to replace in your formula "," with ";"

    If doesn't work

    try to check step by step, for example use a  formula only the last IF condition

    IF(OR([COMBINED CALC]="likelyinsignificant",[COMBINED CALC]="possibleminor",[COMBINED CALC]="unlikelymoderate (medium)",[COMBINED CALC]="raremoderate (medium)"),"moderate","")

    Let us know if it's working


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, January 4, 2017 9:55 AM