Formula to Determine Numeric and Non-Numeric Values

cvarela

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.

cvarela

@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)

jmyzk_cloudsmart_jp

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), ""))))

https://app.smartsheet.com/b/publish?EQBCT=9b86c54915324da1a9902c6c156ed812

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

@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)