Skip to main content
Question

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

  • August 23, 2018
  • 0 replies
  • 107 views
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)))))

    This topic has been closed for replies.

    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.
    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

    • NikkiWalters
    • Author
    • Quickbase Alumni
    • August 24, 2018
    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.
    Terms & ConditionsAccessibility statement