I've got a SP drop down column which contains options like:

  1. Doesn't align to current priorities
  2. Somewhat aligns to current priorities
  3. Aligns strongly with current priorities

I want to assign all of these a numerical value so in this case:

  1. 0
  2. 5
  3. 10

So that these values can be used in a calculated field later. What's the best way of achieving this?

Thanks in advance.

1 Answer 1


Try below formula in your calculated column:

=IF([Choice]="Doesn't align to current priorities","0",IF([Choice]="Somewhat aligns to current priorities","5",IF([Choice]="Aligns strongly with current priorities","10","-")))

Here, [Choice] is the column from which formula compares the values.

See the values in my list view: enter image description here

  • Thanks @Dikesh Gandhi I gave that a try: =IF([[S1]="Doesn't align to either set of objectives","0",IF([S1]="Aligns to 1 objective in the XXX Single Departmental Plan","10", IF([S1]="Aligns to 1 of XXX Priorities","15","-"))) I set the data type returned to number but I'm getting an error saying that the formula refers to a column that does not exist.
    – Dazza
    Apr 7, 2020 at 14:29
  • I am assuming that [S1] is your choice type column in your list and this formula you are adding in calculated column. Apr 8, 2020 at 4:41
  • Yes, I want to use the values assigned to the drop down choices in another Calculated Field
    – Dazza
    Apr 8, 2020 at 7:51
  • 1
    It seems you have an extra bracket in the start of your formula: [[S1] Apr 8, 2020 at 9:49
  • Dikesh' solution worked after a bit of trouble shooting. I took it back to one choice and built from there. Also took the last "-". So for anyone else looking to solve this, it was: =IF([Choice] = "Whatever's in the list","5") or … "Whatever's in the list","5",IF([Choice] = ….pretty much what he said with some tweaks
    – Dazza
    Apr 14, 2020 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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