Average of an If Formula
Hello!
Can anyone provide some insight on why I am unable to get an average on a column of IF formulas?
Here is the formula across the rows in the column: =IF(Status2 = "Not Started", "0%", IF(Status2 = "In Progress", "50%", IF(Status2 = "Completed", "100%")))
I am getting a #DIVIDE BY ZERO error when I use the AVG formula on 10 rows.
When I try to sum the rows and divide by 10, I get 0.000 as the result.
Thank you!
Best Answer

@Lauren E oh yeah that makes sense! You can also do it without value and without quotes:
=IF(Status2 = "Not Started", 0, IF(Status2 = "In Progress", 0.5, IF(Status2 = "Completed", 1)))
Answers

@Lauren E can you provide a screenshot?

Thank you but I actually was able to just figure it out! For anyone else who might come along searching later in a similar situation: I had to remove the % and convert to a value on each of the result values to get it to work.

@Lauren E oh yeah that makes sense! You can also do it without value and without quotes:
=IF(Status2 = "Not Started", 0, IF(Status2 = "In Progress", 0.5, IF(Status2 = "Completed", 1)))

That is good to know! Thank you so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!