Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Jul 28, 2022 08:37 AM
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
Jul 28, 2022 08:46 AM
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*
Jul 28, 2022 09:23 AM
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.
Jul 28, 2022 10:34 AM
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.
Jul 28, 2022 11:23 AM
Hey Zach,
Here’s a pretty in-depth thread on progress bars with different ways to create them.