Formula to Determine Numeric and Non-Numeric Values

Options

Hi all,

I am trying to combine a formula converting a numeric value to a specific format with a check for non-numeric values so the non-numeric values are passed through and don't cause an error. I was able to account for specific non-numeric values however, we have some variability I need to account for.

Formula for converting to the specific format:

=IF([Approved Budget Number]@row = "TBD", "TBD", IF([Approved Budget Number]@row = "N/A", "N/A", IF(ABS([Approved Budget Number]@row) >= 10 ^ 9, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 9, 2) + "B", IF(ABS([Approved Budget Number]@row) >= 10 ^ 6, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 6, 2) + "M", IF(ABS([Approved Budget Number]@row) >= 10 ^ 3, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 3, 2) + "K", ABS([Approved Budget Number]@row))))))

Example of data in the same column that needs passed through:

I used ChatGPT to try to come up with a formula, but the ISNUMBER and ISERROR(VALUE) returned errors.

Tags:

Best Answer

  • cvarela
    cvarela ✭✭✭✭
    Answer ✓
    Options

    @jmyzk_cloudsmart_jp

    Unfortunately your formula left out the formatting for millions and billions which are important for our data. But with the formula you provided and some manipulation for a couple of other text variables that will show up, I was able to get this formula to work. Thank you for the time you put into this!

    =IF(ISNUMBER([Approved Budget Number]@row), IF(ABS([Approved Budget Number]@row) >= 10 ^ 9, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 9, 2) + "B", IF(ABS([Approved Budget Number]@row) >= 10 ^ 6, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 6, 2) + "M", IF(ABS([Approved Budget Number]@row) >= 10 ^ 3, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 3, 2) + "K", IF(CONTAINS("$", [Approved Budget Number]@row), [Approved Budget Number]@row, "")))), [Approved Budget Number]@row)

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @cvarela

    I tested with your sample values, and this formula worked.😀

    =IF(ISNUMBER([Approved Budget Number]@row), IF([Approved Budget Number]@row >= 1000, [Approved Budget Number]@row / 1000 + "K", [Approved Budget Number]@row), IF([Approved Budget Number]@row = "TBD", "TBD", IF([Approved Budget Number]@row = "N/A", "N/A", IF(AND(CONTAINS("$", [Approved Budget Number]@row), CONTAINS("M", [Approved Budget Number]@row)), LEFT([Approved Budget Number]@row, FIND("M", [Approved Budget Number]@row) + 1), ""))))

    If I make the formula readable,

    • =IF(ISNUMBER([Approved Budget Number]@row),
      • IF([Approved Budget Number]@row >= 1000,
        • [Approved Budget Number]@row / 1000 + "K",
        • [Approved Budget Number]@row),
    • # -- following is non-number case --
      • IF([Approved Budget Number]@row = "TBD", "TBD",
      • IF([Approved Budget Number]@row = "N/A", "N/A",
      • IF(AND(
        • CONTAINS("$", [Approved Budget Number]@row),
        • CONTAINS("M", [Approved Budget Number]@row)), # end of AND
        • LEFT([Approved Budget Number]@row, FIND("M", [Approved Budget Number]@row) + 1),
        • "") # Catch all the not a number, not "TBD", not "N/A" and not "$###,###M" case
    • )))


  • cvarela
    cvarela ✭✭✭✭
    Answer ✓
    Options

    @jmyzk_cloudsmart_jp

    Unfortunately your formula left out the formatting for millions and billions which are important for our data. But with the formula you provided and some manipulation for a couple of other text variables that will show up, I was able to get this formula to work. Thank you for the time you put into this!

    =IF(ISNUMBER([Approved Budget Number]@row), IF(ABS([Approved Budget Number]@row) >= 10 ^ 9, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 9, 2) + "B", IF(ABS([Approved Budget Number]@row) >= 10 ^ 6, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 6, 2) + "M", IF(ABS([Approved Budget Number]@row) >= 10 ^ 3, ROUND(ABS([Approved Budget Number]@row) / 10 ^ 3, 2) + "K", IF(CONTAINS("$", [Approved Budget Number]@row), [Approved Budget Number]@row, "")))), [Approved Budget Number]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!