Help

Progress bar in July 2022 AT Video

Topic Labels: Formulas
2714 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Zach_Conrad
6 - Interface Innovator
6 - Interface Innovator

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

image

4 Replies 4

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*

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.

Okay!

Here’s what my finished product looks like:

image

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:

image

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.

image

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:

image

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.

Hey Zach,

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

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable