Calculating percentage based on status options across multiple columns

04/20/21
Answered - Pending Review

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?

Popular Tags:

Answers

  • Cody HolmesCody 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))))

  • 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)))))

  • Hi @Cody Holmes,

    Checking to see how I would integrate counting blank columns as 0 within the formula. I appreciate your help!

Sign In or Register to comment.