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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.