Calculating percentage based on status options across multiple columns

I am trying to average the sum across multiple columns. Each column is a single select dropdown that will allow the user to choose from the following options:

⦸ (Not applicable)

◷ (in progress)

✓ (Complete)

I want to assign values to these symbols.

⦸ = 1

◷ = 0.5

✓ = 1

I have only been able to get the following formula to work:

=AVG(COUNTIF([Column 1]@row = "✓", "1"), (COUNTIF([Column 2]@row = "✓", "1")), (COUNTIF([Column 3]@row = "✓", "1")))

I want to incorporate the other status options into the equation, but I have gotten an error code for everything I try. How would I go about doing this?

Find more posts tagged with

Formulas

Reports

Accepted answers

All comments

Cody Holmes

Hi John,

The following function should be what you're looking for. Keep in mind that you will have to add the conditions for the other symbols into the nested IF statements. Also, if a column is left blank, you could just handle that in the false portions of the IF statements.

=AVG(IF([Column 1]@row = "⦸", 1, IF([Column 1]@row = "◷", 0.5, IF([Column 1]@row = "✓", 1))), IF([Column 2]@row = "⦸", 1, IF([Column 2]@row = "◷", 0.5, IF([Column 2]@row = "✓", 1))), IF([Column 3]@row = "⦸", 1, IF([Column 3]@row = "◷", 0.5, IF([Column 3]@row = "✓", 1))))

John Hageman

Hi Cody,

Thank you for the formula. It is working , but I forgot to mention that the blank columns need to be equal to zero. Using your formula, I am trying to add ISBLANK for each column. I am getting the incorrect syntax error when I use the revised formula:

=AVG(IF(ISBLANK([Column 1]@row), 0, IF([Column 1]@row = "⦸", 1, IF([Column 1]@row = "◷", 0.5, IF([Column 1]@row = "✓", 1)))), IF(ISBLANK([Column 2]@row), 0, ([Column 2]@row = "⦸", 1, IF([Column 2]@row = "◷", 0.5, IF([Column 2]@row = "✓", 1)))), IF(ISBLANK([Column 3]@row), 0, ([Column 3]@row = "⦸", 1, IF([Column 3]@row = "◷", 0.5, IF([Column 3]@row = "✓", 1)))))

John Hageman

Hi @Cody Holmes,

Checking to see how I would integrate counting blank columns as 0 within the formula. I appreciate your 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.