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!
Best 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

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!

Help Article Resources
Categories
Check out the Formula Handbook template!