Calculating percentage based on status options across multiple columns

John Hageman
John Hageman ✭✭
in Formulas and Functions

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?

Tags:
· Share on FacebookShare on Twitter

Answers

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

    · Share on FacebookShare on Twitter
  • John Hageman
    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)))))

    · Share on FacebookShare on Twitter
  • John Hageman
    John Hageman ✭✭

    Hi @Cody Holmes,

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!