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?
Answers

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 444 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!