Percentage Bar Formula

A progress bar? That’s not a native feature of Airtable, but I show off how to do that with a formula in this episode of the BuiltOnAir podcast:

Hey @Maitrik_Makwana!

We actually had a similar thread back in July about this!

Since that thread, I actually revised and refined my final formula found at the bottom of my big post in the thread.

Since it’s an older thread, I can no longer create a revision, but here’s the adjusted formula.

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

You might want to play with the conditions for each increment since they might vary from exactly what you’d like to happen, but the template is sound.

Let me know if you’d like a more in-depth look at how it all works or if you’d like to explore tweaking something.

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

Hey there, Your effort with the above is impressive, but the above can be replaced by this simple formula:

- Calculate the progress in a separate field

- Create a new "Formula" field and enter the following:

Hope this helps!