0

Using: SharePoint Online

How do you assign value to the following choices

Low = 1 Medium = 2 high = 3

enter image description here

Then later on I want to be able to calculate 5 different questions for the total risk rating.

Add 5 scores above together and divide by 5. Score 5-7 = Low; 8-11 = Medium; 12-15 = High

But I'm not even given the option of picking those columns

enter image description here

1 Answer 1

0

Something like this: (the columns are assumed to named Q1,Q2, etc)

If a divide by 5 really works in your example (I don't think it will):

=CHOOSE(
INT(
  IF( Q1="Low", 1, IF( Q1="Medium", 2, IF( Q1="High", 3, 0) ) )
 +IF( Q2="Low", 1, IF( Q2="Medium", 2, IF( Q2="High", 3, 0) ) )  
 +IF( Q3="Low", 1, IF( Q3="Medium", 2, IF( Q3="High", 3, 0) ) )  
 +IF( Q4="Low", 1, IF( Q4="Medium", 2, IF( Q4="High", 3, 0) ) )  
 +IF( Q5="Low", 1, IF( Q5="Medium", 2, IF( Q5="High", 3, 0) ) )  
/ 5 ),
"Low","Medium", "High"

Otherwise:

=CHOOSE(
  IF( Q1="Low", 1, IF( Q1="Medium", 2, IF( Q1="High", 3, 0) ) )
 +IF( Q2="Low", 1, IF( Q2="Medium", 2, IF( Q2="High", 3, 0) ) )  
 +IF( Q3="Low", 1, IF( Q3="Medium", 2, IF( Q3="High", 3, 0) ) )  
 +IF( Q4="Low", 1, IF( Q4="Medium", 2, IF( Q4="High", 3, 0) ) )  
 +IF( Q5="Low", 1, IF( Q5="Medium", 2, IF( Q5="High", 3, 0) ) )  
+ 1, "","","","","","Low","Low","Low","Medium","Medium","Medium","Medium", "High", "High", "High", "High")
  • One was added because CHOOSE does not work with zero as an index.
  • The first "" is for zero (all Q's blank)
  • The next ""'s are for if any of the five questions are blank.
  • Three "low" for 5,6,7
  • Four "medium" for 8,9,10,11
  • Four "High" for 12,13,14,15
2
  • Helpful, but now I'm running into new issue. I added it to my original post. Oct 25, 2018 at 16:00
  • Your screen capture shows that these are "radio buttons", and these should show up for Calculated Columns. If they are "Checkboxes (allow multiple selections)", then they will not show up. I just confirmed that they show up when adding a new Calculated Column using the browser and using SharePoint Designer. Oct 25, 2018 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.