Numeric Formula which allows for "NA" in numeric fields used in calculation

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have an issue on a numeric formula, in which I have the option for users to select 'NA' as part of a dropdown in a numeric field. I built a numeric formula field which calculates the average for these fields, which works fine. However, I'm having issues with my saved form and table not showning that 'NA' was selected.

Example

  • Numeric Field with Multiple Choice:
    • Field Name: A1
      • Multiple Choice Options: 1, 0, NA
    • Field Name: B1
      • Multiple Choice Options: 1, 0, NA
  • Numeric Formula Percent Field:
    • Field Name: Z1
      • Formula: Average([A1],[B1])

If A1 is 1 and B1 is 0, I can get Z1 to state 50%. If A1 is 1 and B1 is NA, I can get Z1 to state 100%. However, my issue when I look at the table after saving the information. In field B1 where I had selected NA is now blank, but my Z1 percentage is still correct.

I need to keep the NA showing in field B1.

Photo of Ken

Ken

  • 50 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
A numeric field is a numeric field and I'm surprised it allowed you to enter NA as a drop down choice.



A suggestion is to have a text field for your drop down choices, but for calculation purposes have a formula numeric field which converts them to numbers.



For example:

Case([my text drop down field]),

"0",0,

"1",1)

I would just remain silent on the NA choice and it will default to being null
Photo of Ken

Ken

  • 50 Points
That worked.  Thank you very much.  I hadn't used the "Case" formula at all, so that will be helpful in the future as well.