This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Progress bar in July 2022 AT Video

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
1978
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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**

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 28, 2022 11:23 AM

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

Hannah - On2Air.com - Automated Backups for Airtable

Reply