2

This is my first time using calculated columns. I have a calculated column that is a number field, that is calculating it's value based on two other columns that are choice columns.

I know that behind the scenes, choice column values are stored as strings. However, in this case, both of the choice column options are just the numbers 1 through 5.

When setting up my calculation formula, will the formula interpret the number string values of the choice fields as numbers, or do I have to treat them as strings?

Specifically, can I treat the value as a number and do

=IF([ChoiceField1]<=2, 0, {nested IF if false})

or do I have to treat the value as a string and do

=IF(OR([ChoiceField1]="1",[ChoiceField1]="2"), 0, {nested IF if false})
  • Same like Excel Formulas or any other programming language. A Text String is not a Number. FYI this CalcMaster Bookmarklet helps a lot in working/testing Formulas – Danny '365CSI' Engelman Jul 29 '15 at 15:31
  • @DannyEngelman, not like any other programming language. Javascript will do implicit conversion, so 1 == "1" is true and 1 + "1" is 2. Hence the reason for my question. – Dylan Cristy Jul 29 '15 at 15:35
  • Darn, there are real programmers hanging around here ;-) – Danny '365CSI' Engelman Jul 29 '15 at 16:59
2

You cannot use it as a number. You have to "convert" your value to numeric in order to make the validation, otherwise it will be "false".

You can use the VALUE function to convert a text representation of a number, or simply add a number to your value to get it interpreted as numeric.

=IF([ChoiceField1]+0<=2, 0, {nested IF if false})

or

=IF(VALUE([ChoiceField1])<=2, 0, {nested IF if false})

The VALUE function Works with number, date or time.

https://support.office.com/en-za/article/VALUE-function-1fc46e51-4a25-4ba0-bce2-512dd5d4d872

  • Interesting. So the formula will do Javascript-style implicit conversion, but only if you add a number to the string value. So strange. – Dylan Cristy Jul 29 '15 at 17:35

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.