Discussions

Expand all | Collapse all

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

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

    Posted 08-22-2018 22:39
    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)))))



  • 2.  RE: Formula to take a rating (text - multiple choice field) and pull the numeric digit out into a numeric field

    Posted 08-23-2018 02:13
    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.


  • 3.  RE: Formula to take a rating (text - multiple choice field) and pull the numeric digit out into a numeric field

    Posted 08-23-2018 17:55
    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



  • 4.  RE: Formula to take a rating (text - multiple choice field) and pull the numeric digit out into a numeric field

    Posted 08-23-2018 20:13
    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?


  • 5.  RE: Formula to take a rating (text - multiple choice field) and pull the numeric digit out into a numeric field

    Posted 08-23-2018 20:19
    No problem on those decimals.  Set the field property on that Rating Numeric field to be say 0 or 1 decimals.