Removing numbers after a decimal place

[ballgame168]

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.

[Julian_E_Post]

Hi Ballgame,
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

[Justin_Barrett]

Agreed. Airtable’s field formatting only controls how a value appears, not how it’s actually calculated. To actually force it to be an integer, you’ll need to round the value.

[ballgame168]

Thanks to both of you for responding. I tried the round function and still getting the same format. I don’t want the currency to convert to an integer - I want it to stay as a currency just without the decimals. And I don’t want to change the current integers I just want the decimals gone. For context, I am calculating revenue for a series of companies and their total employees. I used ROUND({Total Revenue}) and ROUND({Total Employees}) as the functions. Am I doing it wrong?

[Justin_Barrett]

ballgame168:

Am I doing it wrong?

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

[ballgame168]

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?

[kuovonne · Accepted solution]

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.