Progress bar in July 2022 AT Video

[Zach_Conrad]

I’d LOVE to see the formula that Airtable used in their What’s New in Two July 2022 video. Specifically how they handled percentages over 100%. Very cool.

https://www.youtube.com/watch?v=ktkizKuN5bU

[Ben_Young1]

Hey @Zach_Conrad!
Welcome back to the forums!

To be honest, the moment I saw your screenshot, I took a few seconds for an audible, “Woahhhhhhh…” moment.
I had never even considered a formula formatted like this. I now officially have a new toy in my back pocket.

I will experiment with this and return with my findings.

*Excited formula sounds*

[ScottWorld]

Ha, it’s super misleading of them to display a progress bar (which many of their users want), even though progress bars are not officially in the product!

The trick that they used involves emojis.

Note that this only looks right in Chrome — it doesn’t look right in Safari. So if your users are using Safari, it will be disappointing to them.

Let’s say that you want a progress bar with 8 green segments and then the text 80% afterwards. The formula would look like this:

REPT("🟩",8) & " 80%"

If you wanted to show a progress bar with 12 red segments and then the text 120% afterwards, it would look like this:

REPT("🟥",12) & " 120%"

Of course, you would swap out the numbers (8 and 12) and the text (80% and 120%) with either formulas or dynamic field values.

And you could make an even lengthier formula by combining all of these different options. For example, if you wanted red emojis to show up if you go over 100% for a project, but green emojis to show up if you are at 100% or less.

But once again, this only works properly in Chrome.

[Ben_Young1]

Okay!

Here’s what my finished product looks like:

As @ScottWorld pointed out, the browser differences matter when working with formulas that dynamically output emojis.
I use the desktop native Airtable application. For reference, this is what it looks like in Safari:

Now, it’s far from the biggest or most complex formula I’ve ever written, but it’s still a beast.

IF(
    AND(
        {Estimate},
        {Actual}
    ),
    IF(
        {Amount} <= 10,
        "🟩 " & FLOOR({Amount}, 0.01) & "%",
        IF(
            {Amount} <= 20,
            "🟩🟩 " & FLOOR({Amount}, 0.01) & "%",
            IF(
                {Amount} <= 30,
                REPT('🟩', 3) & " " & FLOOR({Amount}, 0.01) & "%",
                IF(
                    {Amount} <= 40,
                    REPT('🟩', 4) & " " & FLOOR({Amount}, 0.01) & "%",
                    IF(
                        {Amount} <= 50,
                        REPT('🟩', 5) & " " & FLOOR({Amount}, 0.01) & "%",
                        IF(
                            {Amount} <= 60,
                            REPT('🟩', 6) & " " & FLOOR({Amount}, 0.01) & "%",
                            IF(
                                {Amount} <= 70,
                                REPT('🟩', 7) & " " & FLOOR({Amount}, 0.01) & "%",
                                IF(
                                    {Amount} <= 80,
                                    REPT('🟩', 8) & " " & FLOOR({Amount}, 0.01) & "%",
                                    IF(
                                        {Amount} <= 90,
                                        REPT('🟩', 9) & " " & FLOOR({Amount}, 0.01) & "%",
                                        IF(
                                            OR(
                                                {Amount} <= 100,
                                                {Amount} > 100
                                            ),
                                            REPT('🟥', 10) & " " & FLOOR({Amount}, 0.01) & "%"
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    ),
    IF(
        OR(
            AND(
                NOT({Estimate}),
                NOT({Actual})
            ),
            AND(
                {Estimate} = 0,
                {Actual} = 0
            )
        ),
        "🔲  0%" 
    )
)

To keep the formula kinda tame, I created another hidden formula field that calculates the percentage as an integer which is then referenced by the formula.

Since the Amount formula field is just formatting the actual value, there’s no harm in just keeping it formatted as an integer and hiding it.

---

I originally wrote the formula without the Amount field in mind; here’s the version of the formula that I would personally use:

IF(
    AND(
        {Estimate},
        {Actual}
    ),
    IF(
        (({Actual} / {Estimate}) * 100) <= 10,
        "🟩 " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
        IF(
            (({Actual} / {Estimate}) * 100) <= 20,
            "🟩🟩 " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
            IF(
                (({Actual} / {Estimate}) * 100) <= 30,
                REPT('🟩', 3) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                IF(
                    (({Actual} / {Estimate}) * 100) <= 40,
                    REPT('🟩', 4) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                    IF(
                        (({Actual} / {Estimate}) * 100) <= 50,
                        REPT('🟩', 5) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                        IF(
                            (({Actual} / {Estimate}) * 100) <= 60,
                            REPT('🟩', 6) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                            IF(
                                (({Actual} / {Estimate}) * 100) <= 70,
                                REPT('🟩', 7) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                                IF(
                                    (({Actual} / {Estimate}) * 100) <= 80,
                                    REPT('🟩', 8) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                                    IF(
                                        (({Actual} / {Estimate}) * 100) <= 90,
                                        REPT('🟩', 9) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%",
                                        IF(
                                            OR(
                                                (({Actual} / {Estimate}) * 100) <= 100,
                                                (({Actual} / {Estimate}) * 100) > 100
                                            ),
                                            REPT('🟥', 10) & " " & FLOOR(({Actual} / {Estimate}) * 100, 0.01) & "%"
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    ),
    IF(
        OR(
            AND(
                NOT({Estimate}),
                NOT({Actual})
            ),
            AND(
                {Estimate} = 0,
                {Actual} = 0
            )
        ),
        "🔲  0%" 
    )
)

Here, you can see that this formula behaves identically to the other version:

Honestly, while this formula works and is pretty much now modular enough to where you can use it almost universally, it is probably more efficient to just use a script, but it’s up to you on how you want to approach it.

[Hannah_Wiginton]

Hey Zach,

Here’s a pretty in-depth thread on progress bars with different ways to create them.

In-Cell Progress Bar for Each Record Product Suggestions

Hi @Lindsey_Bavaro and @ScottWorld , These ‘emojis’ are a little different in that they are like another type of text character. So like ‘a’ ‘b’ and ‘c’ are all text characters, so are ‘§○•◄►♪’. (This is over simplification but I think gets the idea across) The color is then decided by the text color you are using in whatever tool you are typing in: MS Word/Google Docs, for example. On Windows you can use the ‘alt code’ to type the different characters using the ALT button and the number pad. …