Formula to Determine Numeric and Non-Numeric Values

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.

Find more posts tagged with

Formulas

Accepted answers

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)

All comments

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

briandoe

Wouldnt it be better for smartsheet to allow us to specify a "Numeric" column rather then Text/Numeric?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.