Formula to take a rating (text - multiple choice field) and pull the numeric digit out into a numeric field

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
I have a text field - multiple choice rating field [Rating Category] with the list of choices as shown in the formula below. I need a second field, numeric, so I can total & average the ratings.  I set up this formula in a numeric formula field, but it's not working.  What am i doing wrong?
  • If(Contains([Rating Category],"0 - Sellers with <1 Year"),1,null,
  • If(Contains([Rating Category],"1 - No Performance Issues"),1,null,
  • If(Contains([Rating Category],"2 - No Near-term Performance Issues"),2,null,
  • If(Contains([Rating Category],"3 - Manager Performance Plan"),3,null,
  • If(Contains([Rating Category],"4 - Performance Plan "),4,null)))))

Photo of Nikki


  • 284 Points 250 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Try this

Begins([Rating Category],"0"),1,
Begins([Rating Category],"1"),2,
Begins([Rating Category],"2"),2,
Begins([Rating Category],"3"),3,
Begins([Rating Category],"4"),4)

Note that this will calculate to null if none of the conditions are met and I'm pretty sure that you will still be able to run an Average on that value and it will ignore nulls.
To your question about what you've got wrong, Nikki, your IF syntax has too many arguments. Rather than start each condition with a new IF() wrapped with its own 'else' result (null in your case), you just want one IF(), with your [condition],[result] pairs sequenced with commas. Compare your syntax to the one Coach provided.

Quickbase IF syntax doesn't require you to "nest" the IFs.

You could also save keystrokes by using CASE() instead of IF(). Here's the function reference for CASE()

Photo of Nikki


  • 284 Points 250 badge 2x thumb
Thank you both.  Alex, very helpful for future formulas.  Mark, worked like a charm.  The average seems to be working too, but is there a way to reduce the decimal points in a summary report?
No problem on those decimals.  Set the field property on that Rating Numeric field to be say 0 or 1 decimals.