Formula for Currency - Rounding

ballgame168 · ‎Nov 29, 2020

I found the following formula I am using to format currency. I want to round up or down and get rid of the decimals . Any ideas on how to modify the formula to do that? Thanks!

IF(
LEFT({NumberValue}&’’,1,1)=’-’,
‘-’,
‘’)&’$’&
IF(
ABS(VALUE({NumberValue}&’’))>=1000000,
INT(ABS(VALUE({NumberValue}&’’))/1000000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)),
IF(
ABS(VALUE({NumberValue}&’’))>=1000,
INT(ABS(VALUE({NumberValue}&’’))/1000)&’,’&
IF(
INT(MOD(VALUE({NumberValue}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)),
INT(ABS(VALUE({NumberValue}&’’)))))&’.’&
IF(
LEN(ROUND(MOD(VALUE({NumberValue}&’’),1)*100,0)&’’)<2,
‘0’,
‘’
)&
ROUND(MOD(VALUE({NumberValue}&’’),1)*100)

ballgame168 · ‎Nov 30, 2020

I was able to get this formula to work and remove the decimals by removing the last IF statement:

(IF(
LEN(ROUND(MOD(VALUE({NumberValue}&’’),1)*100,0)&’’)<2,
‘0’,
‘’
)&
ROUND(MOD(VALUE({NumberValue}&’’),1)*100)

But now it automatically rounds every number down instead of rounding based on the value. Any suggestions on how to modify the formula in my original post to round appropriately?

Justin_Barrett · ‎Dec 04, 2020

What field type is {NumberValue}? I’m curious why you need to force it to a string for your calculations, including places where it’s forced to a string and then back to a number using VALUE().

ballgame168 · ‎Dec 05, 2020

Thanks, @Justin_Barrett. The field type is currency formatted to 1 as the precision. It formats fine in Airtable. However, I am using Stacker for the the front-end and when the value (without the formula above) gets passed to Stacker the formatting goes away and I am left with the amount including decimals. So I created a an additional field with the formula above and {NumberValue} which works when passed to Stacker. I’ve included a screenshot below. You’ll notice the first 3 records round down (e.g. $89 -> $88, $141 -> $140, and $161 -> $160), but the 4th record does not ($351 -> $351). I suspect this is because the 4th record was somewhere between $351.00 and $351.49 while the first 3 records were greater than .5 (e.g. $88.70). Really appreciate your help.

Justin_Barrett · ‎Dec 10, 2020

Thanks for the update (and apologies for the delay), but your screenshot doesn’t show the {NumberValue} field, so I’m still not certain how that plays into this. The actual number (formatted via Airtable’s field formatting) is already in the {Per Participant RK Fee ($)} field, so why can’t that be directly used in the other formula? Why is {NumberValue} necessary to make this work?

On top of that, the formula you listed in your first post is fairly complex because it’s designed for larger numbers, but in your screenshot, none of the numbers is larger than three digits. What’s the largest number that you anticipate needing to format? If it’s under $1000 (and never negative), then you can get by with a much simpler formula.

ballgame168 · ‎Dec 11, 2020

@Justin_Barrett Sorry for the confusion. The {Per Participant RK Fee ($)} is actually used in the formula instead of {NumberValue}. I just used {NumberValue} as an example - it doesn’t actually exist in my Airtable or the formula. So as an example, the formula would actually look something like then when that field is referenced.

(IF(
LEN(ROUND(MOD(VALUE({Per Participant RK Fee ()}&’’),1)*100,0)&’’)<2, ‘0’, ‘’ )& ROUND(MOD(VALUE({Per Participant RK Fee ()}&’’),1)*100)

You bring up a good point about the complexity of the formula. In my base, I am dealing with very large $ numbers, into the billions which is why I need it, and most which go into 5 and 6 figures. Other fields like this one will never get above $1,000 so maybe I should user a simpler formula in that case. Any suggestions? Lastly, any thoughts on how to amend my formula to avoid rounding down every time? Very grateful for your continued help.

Justin_Barrett · ‎Dec 11, 2020

Gotcha. I had a hunch that might be the case. :slightly_smiling_face: Even though the real field name is more complex, I’ll use that going forward, as it means you won’t have to convert to it on your end.

Before digging into the rounding issue, I want to address something else that I touched on earlier. Whenever you reference that field, it currently looks like this:

VALUE({Per Participant RK Fee ($)} & '')

I’m not sure if you realize it, but this structure takes the number in the {Per Participant RK Fee ($)} field, converts it into a string, then back into a number, which is needlessly redundant. You said at the outset that you found the formula you’re using, so clearly this isn’t a misunderstanding on your part, but I still want to make you aware of it. Any modifications I suggest going forward will have that redundancy stripped out so that it’s just the field reference without the back-and-forth conversion.

I’ll have to review the rounding issues later once I’ve tackled some other things on my plate.

ballgame168 · ‎Dec 11, 2020

Thanks, @Justin_Barrett. The reason I am converting it to a string and then back into a number is because of Stacker. While Airtable rounds the number from a formatting perspective, it passes through the decimals to Stacker which I don’t want to be displayed. I just want round numbers displayed in Stacker. This is the only way I could figure out how to do it - Airtable makes it unnecessarily complex in my opinion. So really I have 3 goals (the first 2 I accomplished with my formula, even if complex) and the third I am stuck on. I’d love to accomplish all 3 in a simpler way if possible.

Represent a currency with no decimals
Add commas to the number when necessary (e.g. $100,000,000 instead of $100000000)
Round the amount up/down as necessary

Justin_Barrett · ‎Dec 11, 2020

I understand that your end goal is to get a formatted number that you can display in Stacker, but the number-to-string-to-number conversion that’s done so much in the formula has nothing to do with Stacker, and (as far as I know) has no meaningful bearing on the outcome of that formula. A few minutes ago I found the source of that formula—@W_Vann_Hall’s pretty-print routine—but I’m still confused why he chose to include the number-string-number conversion as part of the process.

Say you have a field that contains the value $101.30. Whether that was manually input or generated by a formula makes no difference. Internally, all that’s stored is 101.3, but the field formatting is set to display it as currency, which adds the dollar sign and pads out the trailing zeroes, so what you see is $101.30. Converting that to a string will create “101.3” because the conversion is done on the internally-stored number, not the formatted version you see in the field. Converting that string back to a number using the VALUE() function will return it back to 101.3. In short, it goes from 101.3 to “101.3” to 101.3. That’s the redundancy I’m talking about, and that’s happening over and over inside that formula for a reason I don’t understand. It’s possible that it exists to address something else that @W_Vann_Hall wanted to cover, and it’s worth noting that his formulas were designed to cover a broad range of use cases, so that may partly explain it. It doesn’t really affect the outcome or slow down the formula’s execution, but it also doesn’t contribute meaningfully to the end result. With all due respect to Mr. Hall, it’s programmatic clutter for this particular use case, and I prefer to strip out clutter wherever possible.

Justin_Barrett · ‎Dec 14, 2020

This was trickier than I thought, partly because I had to reverse-engineer not only what the original code does, but why it does it. That said, I got this working successfully in a test table:

The {Airtable Formatted} field just echoes the number from {Value}, and formats the field using the Currency option with precision set to $1, which auto-rounds for display purposes. That’s what I knew I had to match when building the formatted number.

You said that your values could be into the billions, so that alone meant reworking the formula a bit because it was only designed to format numbers into the millions.

The first thing I did was take care of the rounding outside of the main formula, leaving the main formula to format that already-rounded number. I made a formula field named {Rounded}, using this formula, then hid it from view:

ROUND(Value)

The main formula in the {Formula Formatted} field is this:

IF(
    LEFT(Rounded & '', 1) = '-',
    '-'
) & '$' &
IF(
    ABS(Rounded) >= 1000000000,
    INT(ABS(Rounded) / 1000000000) & ',' &
    IF(
        INT(MOD(Rounded, 1000000000) / 1000000) < 100,
        REPT('0', 3 - LEN(INT(MOD(Rounded, 1000000000) / 1000000)))
    ) & INT(MOD(Rounded, 1000000000) / 1000000) & ',' &
    IF(
        INT(MOD(Rounded, 1000000) / 1000) < 100,
        REPT('0', 3 - LEN(INT(MOD(Rounded, 1000000) / 1000)))
    ) & INT(MOD(Rounded, 1000000) / 1000) & ',' &
    IF(
        INT(MOD(Rounded, 1000))<100,
        REPT('0', 3 - LEN(INT(MOD(Rounded, 1000))))
    ) & INT(MOD(Rounded, 1000)),
    IF(
        ABS(Rounded) >= 1000000,
        INT(ABS(Rounded) / 1000000) & ',' &
        IF(
            INT(MOD(Rounded, 1000000) / 1000) < 100,
            REPT('0', 3 - LEN(INT(MOD(Rounded, 1000000) / 1000)))
        ) & INT(MOD(Rounded, 1000000) / 1000) & ',' &
        IF(
            INT(MOD(Rounded, 1000))<100,
            REPT('0', 3 - LEN(INT(MOD(Rounded, 1000))))
        ) & INT(MOD(Rounded, 1000)),
        IF(
            ABS(Rounded)>=1000,
            INT(ABS(Rounded) / 1000) & ',' &
            IF(
                INT(MOD(Rounded, 1000))<100,
                REPT('0', 3 - LEN(INT(MOD(Rounded, 1000))))
            ) & INT(MOD(Rounded,1000)),
            INT(ABS(Rounded))
        )
    )
)