This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Removing numbers after a decimal place

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

1
3187
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 26, 2020 04:09 AM

I am using Stacker as a front end for my Airtable base. In Airtable, when I use the rollup and formula functions to calculate or show currency data that I format with precision of 1 show no decimals are showing. Also, i do the same with integer data that I format with no decimals. However, when the data is pulled into Stacker it shows the decimals for each. So the value of 54,528,713 in Airtable displays as $54,528,713.48 in Stacker and the value of 180 is Airtable displays as 180.32 in Stacker. Does anyone know of a way to fix this or use a formula (like the TRUNC function in Excel)? If so, I figure I could add a field next to each field that needs the decimal removed and use the this function to create the value with no decimals and then pull that into Stacker. Thanks for any insights.

Solved! Go to Solution.

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 29, 2020 09:34 AM

The “pretty print” formula in this thread can give you an idea of how difficult it is to format a number. It can also give you ideas about a different approach.

However, due to the nature of formula fields, it is impossible to have a formula field that does not have a limit.

Reply

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 26, 2020 06:22 AM

Like you suggested, I’d suspect you’re seeing the difference between the number that Airtable has stored (decimal) and the formatting that you specified (integer). Try using the Round() function

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 26, 2020 06:33 AM

*appears*, not how it’s actually *calculated*. To actually force it to be an integer, you’ll need to round the value.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 26, 2020 09:09 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 26, 2020 11:09 AM

No. My guess is that the data passed from Airtable to Stacker (which I’ve never used, so I can’t comment on any specifics from that end of things) is still a decimal value. All that the `ROUND()`

function will do is round to the nearest whole number. For example, if the calculated value is $54,528,713.48, using `ROUND()`

will turn it into $54,528,713.00. However, If Airtable is still passing that number to Stacker as a decimal value, that’s not something you can control. The only thing you *might* be able to control (see my earlier disclaimer) is the formatting in Stacker.

Another way around this is to convert the number into a string, which can be formatted any way you wish before it’s passed to Stacker. The only issue there is that Airtable doesn’t provide any formula functions for formatting currency. Turning $54,528,713.00 directly into a string will yield 54528713. You’d have to build a formula to add the leading dollar sign and separating commas, and make it smart enough to work with numbers of varying sizes. This topic has been covered in other threads, and a quick search of the forum will likely yield several solutions ranging from the simple to the complex. (I don’t have a go-to formula along this line because it’s not something I do much, or else I would’ve already added it here.)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 29, 2020 06:41 AM

Thank you, Justin!. I found a formula and implemented it but it’s a pain. I have to create 3 additional fields to do it. But it works (for the most part). After I get the string, I have a formula (below) to add commas, and then a field to concatenate the number with commas and $. I have run into an issue with the formula and I’m stuck. Perhaps you could tell me what I am missing. Here’s a sample formula:

IF(LEN({My Total Assets (Static)}) < 4, {My Total Assets (Static)},

IF(AND(LEN({My Total Assets (Static)}) >= 4, LEN({My Total Assets (Static)}) <= 6),

LEFT({My Total Assets (Static)}, LEN({My Total Assets (Static)}) - 3)&","&RIGHT({My Total Assets (Static)},3),

IF(AND(LEN({My Total Assets (Static)}) >= 7, LEN({My Total Assets (Static)}) <= 9),

LEFT({My Total Assets (Static)}, LEN({My Total Assets (Static)}) - 6)&","&MID({My Total Assets (Static)},LEN({My Total Assets (Static)}) - 5 ,3)&","&RIGHT({My Total Assets (Static)},3),

“Exceeded Formula Limit (999,999,999)”

)))

My issue is that on some occasions, the exceeded formula limit is being hit. I don’t want there to be a limit but I am missing the right parts of the formula to extend out the numbers.

Any ideas?

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 29, 2020 09:34 AM

The “pretty print” formula in this thread can give you an idea of how difficult it is to format a number. It can also give you ideas about a different approach.

However, due to the nature of formula fields, it is impossible to have a formula field that does not have a limit.

Reply