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

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
  • 0
  • 1
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

Nikki

  • 240 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
6 months ago
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
Try this


IF(
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.
6 months ago
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() https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=136&rl=f6g

6 months ago
Photo of Nikki

Nikki

  • 240 Points 100 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?
6 months ago
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
No problem on those decimals.  Set the field property on that Rating Numeric field to be say 0 or 1 decimals.
  • 0
  • 1

Powered by Get Satisfaction