Help

Re: Percentage Bar Formula

1848 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Maitrik_Makwana
4 - Data Explorer
4 - Data Explorer

image

I want a percentage bar where if desktop, tablet and mobile is ticked then it will show full 100% filled percentage bar

Here’s my table link:

3 Replies 3

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.

MoNasser
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

 

 

IF(Progress=0,"🔲0%",IF(Progress<.5,REPT("🟥",FLOOR(Progress*10))&Progress*100&"%",IF(AND(Progress>=.5,Progress<.9),REPT("🟧",FLOOR(Progress*10))&Progress*100&"%",REPT("🟩",FLOOR(Progress*10))&Progress*100&"%")))

 

 

Hope this helps!