Forum Discussion
Help with calculated column in SharePoint LIST
I'm not sure if this is possible but please help if you can
trying to move the following risk MATRIX from excel into SharePoint list
created 2 choice columns for Impact & Probability (1 to 7)
I need formula to populate 3rd column (Risk level with correct number or text it doesn't matter)
Examples 1- if Imp = 2 , PROBALIBTY =4 , Risk level is 5 ?
neeesz78 this was an interesting challenge because there is a limit of 19 nested ifs in a calculated column formula in SharePoint. So a calculated column is not posible here. However it is possible to have more nested ifs in a Power Apps customised form formula and we can just squeeze in the number you need in before it errors.
My SharePoint list uses the Title column for the risk item and has a choice column (1-7) for the Impact, another chpoice column (1-7) for the Probability and a single line of text column for the reisk level.
Add the Power Apps customised form:
I've renamed the impact dropdown to dataImpact and the probability dropdown to dataProbability:
For the risk level change the default property of the data field to the nested if statement shown below:
If(dataImpact.Selected.Value="1", "Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="2"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="3"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="4"),"Level 5", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="5"),"Level 5", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="6"),"Level 4", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="7"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="2"),"Level 5", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="3"),"Level 5", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="4"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="5"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="6"),"Level 3", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="7"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="2"),"Level 5", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="3"),"Level 4", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="4"),"Level 4", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="5"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="6"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="7"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="1"),"Level 5", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="2"),"Level 4", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="4"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="5"),"Level 2", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="6"),"Level 2", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="7"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="1"),"Level 4", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="2"),"Level 3", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="4"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="5"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="6"),"Level 1", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="7"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="1"),"Level 4", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="2"),"Level 3", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="4"),"Level 2", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="5"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="6"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="7"),"Level 1", "Error" )))))))))))))))))))))))))))))))))))))))))))That will give the following result when you add the items into the list:
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
3 Replies
neeesz78 this was an interesting challenge because there is a limit of 19 nested ifs in a calculated column formula in SharePoint. So a calculated column is not posible here. However it is possible to have more nested ifs in a Power Apps customised form formula and we can just squeeze in the number you need in before it errors.
My SharePoint list uses the Title column for the risk item and has a choice column (1-7) for the Impact, another chpoice column (1-7) for the Probability and a single line of text column for the reisk level.
Add the Power Apps customised form:
I've renamed the impact dropdown to dataImpact and the probability dropdown to dataProbability:
For the risk level change the default property of the data field to the nested if statement shown below:
If(dataImpact.Selected.Value="1", "Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="2"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="3"),"Level 6", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="4"),"Level 5", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="5"),"Level 5", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="6"),"Level 4", If((dataImpact.Selected.Value="2" && dataProbability.Selected.Value="7"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="2"),"Level 5", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="3"),"Level 5", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="4"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="5"),"Level 4", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="6"),"Level 3", If((dataImpact.Selected.Value="3" && dataProbability.Selected.Value="7"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="1"),"Level 6", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="2"),"Level 5", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="3"),"Level 4", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="4"),"Level 4", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="5"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="6"),"Level 3", If((dataImpact.Selected.Value="4" && dataProbability.Selected.Value="7"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="1"),"Level 5", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="2"),"Level 4", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="4"),"Level 3", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="5"),"Level 2", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="6"),"Level 2", If((dataImpact.Selected.Value="5" && dataProbability.Selected.Value="7"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="1"),"Level 4", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="2"),"Level 3", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="4"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="5"),"Level 2", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="6"),"Level 1", If((dataImpact.Selected.Value="6" && dataProbability.Selected.Value="7"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="1"),"Level 4", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="2"),"Level 3", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="3"),"Level 3", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="4"),"Level 2", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="5"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="6"),"Level 1", If((dataImpact.Selected.Value="7" && dataProbability.Selected.Value="7"),"Level 1", "Error" )))))))))))))))))))))))))))))))))))))))))))That will give the following result when you add the items into the list:
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)- RobElliott , this was very helpful , many thanks it worked perfectly when using small LIST.
its just that my original LIST is a bit more complicated than 4 columns
Noticed the customized FORM is a bit slow
Just need to work on organizing my FIELDS
but again many thanksneeesz78 yes all those nested ifs do slow it down.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
