I Need Help with IF Formula for Progress Bar Symbol Column to Function Accordingly


I'm trying to set up our Carpentry Shop's Progress Bar by how many hours they've used from the total hours they were assigned to use for the project. The following screenshot shows an example. The carpentry shop was assigned a total of 80 hours to use. So far they've used 40 hours. The progress bar's formula then updates to the "Half" symbol. I was able to get this formula to function:

=IF([Total Carp Used Hrs.]774 = 0, "Empty", IF([Total Carp Hrs.]774 <= [Total Carp Used Hrs.]774, "Full", IF([Total Carp Used Hrs.]774 = 1 / 2, [Total Carp Hrs.]774, "Half")))

But when I try to set up the "Quarter" and the "Three Quarter", the cell is giving me an INCORRECT ARGUMENT result:

=IF([Total Carp Used Hrs.]772 = 0, "Empty", IF([Total Carp Hrs.]772 <= [Total Carp Used Hrs.]772, "Full", IF([Total Carp Used Hrs.]772 = 1 / 2, [Total Carp Hrs.]772, "Half", IF([Total Carp Used Hrs.]772 = 1 / 4, [Total Carp Hrs.]772, "Quarter", IF([Total Carp Used Hrs.]772 = 3 / 4, [Total Carp Hrs.]772, "Three Quarter")))))

Maybe I'm using the wrong formula?? Can anyone help?

Thank you


  • Doin_Smart_sheet

    I think I may have found a way for this to work how you are wanting.

    I'm sure there may be other ways but I will share the details below on this rout.

    I generated a percentage column with a formula column that divides the hours used by the Total hours to give decimal figures.

    after creating the percentage column ran the following formula and set it to column formula

    =IF([percentage]@row = 0, "Empty", IF(AND([percentage]@row > .01, [percentage]@row < .49), "Quarter", IF(AND([percentage]@row > .49, [percentage]@row < .75), "Half", IF(AND([percentage]@row > .74, [percentage]@row < .99), "Three Quarter", IF([percentage]@row >= 1, "Full", "")))))

    Did a couple tests to make sure it worked and seems to be working well so then hid the column and tested a few more, this may work well for what you may need.

    Hope this finds you and is helpful have a great day.

  • DEGUC010

    @Doin_Smart_sheet That worked perfectly! Thank you so much 😄

  • Doin_Smart_sheet

    @DEGUC010 that's great news hope your day is prosperous. 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!