Is there a way to simplify this formula?

I have 3 columns with dropdowns of 'Complete' 'In progress' and 'Not Started'.

In a fourth column (Percentage of Task Complete) I have a formula that assigns a value to each of those text items and then does an average and converts it to a percentage. Here is the formula:

=(SUM(IF([Step 1]@row = "complete", 100, IF([Step 1]@row = "in progress", 50, (IF([Step 1]@row = "not started", 0)))), IF([Step 2]@row = "complete", 100, IF([Step 2]@row = "in progress", 50, (IF([Step 2]@row = "not started", 0)))), IF([Step 3]@row = "complete", 100, IF([Step 3]@row = "in progress", 50, (IF([Step 3]@row = "not started", 0))))) / 3) * 0.01

Is there a way to simplify this formula? Or an easier method than what I've done?

Thanks!



Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a number of different ways to accomplish this process, but "simplified" would be a matter of opinion when it comes to the other options.


    You could use helper columns to house each IF and then sum the helper columns.

    You could use a COUNTIFS to count how many across the row are that value and then multiply by the appropriate number and then sum those.

    Or you could use a combination of the two and have a helper column for each status instead of each step and then sum those.

    =COUNTIFS([Step 1]@row:[Step 3]@row, "Complete") * 100

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a number of different ways to accomplish this process, but "simplified" would be a matter of opinion when it comes to the other options.


    You could use helper columns to house each IF and then sum the helper columns.

    You could use a COUNTIFS to count how many across the row are that value and then multiply by the appropriate number and then sum those.

    Or you could use a combination of the two and have a helper column for each status instead of each step and then sum those.

    =COUNTIFS([Step 1]@row:[Step 3]@row, "Complete") * 100

  • Thanks, Paul, I appreciate your feedback.

    I may just leave it as is for now, but I'll def keep these ideas in mind for the future.

    Thanks again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!