# 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:

• ✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
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
• )))

• ✭✭✭✭