Progress Bar Formula

Options

Hello!

I am trying to create a formula for the progress bar which will change according to the state and % complete. The following formula is what I have come up with but it doesn't not seem to be working. Is IF/And required here?

Debbie

Best Answer

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓
    Options

    @Deborah Berwaldt , my bad, I missed the @row needed at the end of the [% Complete] column.

    Give this a try:

    =IF(AND(State@row = "Blue", [% Complete]@row = 0),"Empty",IF(AND(State@row = "Green", [% Complete]@row = 0.25),"Quarter",IF(AND(State@row = "Green", [% Complete]@row = 0.5),"Half",IF(AND(State@row = "Green", [% Complete]@row = 0.75),"Three Quarter",IF(AND(State@row = "Blue", [% Complete]@row = 1),"Full")))))

    One thing I did spot (likely a business logic point) is the combinations of state and % complete appears to have some gaps. For example, if state is blue and % complete is 50% then the progress column won't show anything, but that may not be a valid combination in your world.

    Let me know if that covers the conditions you're looking for.

    Thanks!

    Sing Chen

    Process Architect, Dayforce

    LinkedIn

Answers

  • Sing C
    Sing C ✭✭✭✭✭✭
    Options

    Hi @Deborah Berwaldt,

    I hope you're well today.

    At first glance, it appears that the first condition in the IF statement is missing the AND wrapper that the rest have:

    =IF(AND(State@row = "Blue",[% Complete]=0), "Empty"......


    I hope that helps. Let me know if that achieves the desired result.

    Thanks,

    Sing

    Sing Chen

    Process Architect, Dayforce

    LinkedIn

  • Deborah Berwaldt
    Options

    @Sing C Thanks for your thoughts on this but unfortunately the formula is still not working. Any other suggestions?

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓
    Options

    @Deborah Berwaldt , my bad, I missed the @row needed at the end of the [% Complete] column.

    Give this a try:

    =IF(AND(State@row = "Blue", [% Complete]@row = 0),"Empty",IF(AND(State@row = "Green", [% Complete]@row = 0.25),"Quarter",IF(AND(State@row = "Green", [% Complete]@row = 0.5),"Half",IF(AND(State@row = "Green", [% Complete]@row = 0.75),"Three Quarter",IF(AND(State@row = "Blue", [% Complete]@row = 1),"Full")))))

    One thing I did spot (likely a business logic point) is the combinations of state and % complete appears to have some gaps. For example, if state is blue and % complete is 50% then the progress column won't show anything, but that may not be a valid combination in your world.

    Let me know if that covers the conditions you're looking for.

    Thanks!

    Sing Chen

    Process Architect, Dayforce

    LinkedIn

  • Deborah Berwaldt
    Options

    Thank you! This worked perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!