Is there a way to simplify this formula?

Taylor Scott

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!

Paul Newcome

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

Paul Newcome

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

Taylor Scott

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

Happy to help. 👍️