How to make a choice in a dropdown box have words that also equal integers?

Question

I am trying to create a list that has three columns. There is a dropdown, so the users can choose whether something is "High", "Medium", or "Low". I want to be able to have these choices equal a number for example "High" = 3.

The final goal is to be able to have a fourth column be able to multiply the values from the first three columns while retaining the words. So the formula for column 4 would be [(Column 1*Column 2*Column 3) = #].

Is this possible?

Answer 1

Yes this is possible.

1. You can make 3 Calculated columns to spit out the number value of each of the three columns you talk about, and then have the 4th Calculated column use those values.

Or

2. You can make a long formula for your 4th column to first determine the corresponding value of "High", "Medium", "Low" for each column before multiplying the values.

I'd suggest the first approach to ensure simplicity.

1. Would be to have
   • Column1, CalculatedColumn1 (Formula => =IF([Column1]="High",3,IF([Column1]="Medium",2,IF([Column1]="Low",1))))
   • Column2, CalculatedColumn2 (Formula => =IF([Column2]="High",3,IF([Column2]="Medium",2,IF([Column2]="Low",1))))
   • Column3, CalculatedColumn3 (Formula => =IF([Column3]="High",3,IF([Column3]="Medium",2,IF([Column3]="Low",1))))
   • Column4 (Formula => =[CalculatedColumn1]*[CalculatedColumn2 ]*[CalculatedColumn3])

Answer 2

The default SharePoint Priority field has the (1) Low, (2) Medium, (3) High notation for the labels, that makes it easier to extract the value and your fourth Calculated Column slims down to:

=RIGHT(LEFT([Col1],2)) * RIGHT(LEFT([Col2],2)) * RIGHT(LEFT([Col3],2))