AVG(IF) statement based on criteria
Hi Team, Can you please help me write a formulae wherein each KPI has 4 options in dropdown value each representing a certain value as below
Partially Met = 100%, Met = 100%, Not Met = 0%
How do bring an average value under Score based on the selection in the three KPI. I have entered the Score %s for your reference so the formulae output can match the same.
Can you please help.
Answers

Trying using this:
=AVG(IF([KPI  A]@row = "Partially Met", 1, IF([KPI  A]@row = "Met", 1, IF([KPI  A]@row = "Not applicable", 0))), IF([KPI  B]@row = "Partially Met", 1, IF([KPI  B]@row = "Met", 1, IF([KPI  B]@row = "Not applicable", 0))), IF([KPI  C]@row = "Partially Met", 1, IF([KPI  C]@row = "Met", 1, IF([KPI  C]@row = "Not applicable", 0))))

Thank you for sharing the same. There are two elements which have to be considered.
 There are 4 dropdown values. Met (100%), Not Met(0%), Partially met (50%) and Not applicable
 Considering the selection the range for Average would change, meaning  if 1st and 2nd KPI have some sort of met and 3rd KPI is Not applicable, then the avg would only divide the first two values by 2 and not 3.
Can you please look into the same.

Hi, @Gaurav Chauhan , this should work,
=IFERROR(SUM(IF([KPI  A]@row = "Partially Met", 0.5, IF([KPI  A]@row = "Met", 1, 0)), IF([KPI  B]@row = "Partially Met", 0.5, IF([KPI  B]@row = "Met", 1, 0)), IF([KPI  C]@row = "Partially Met", 0.5, IF([KPI  C]@row = "Met", 1, 0))) / SUM(IF([KPI  A]@row = "Not Applicable", 0, 1), IF([KPI  B]@row = "Not Applicable", 0, 1), IF([KPI  C]@row = "Not Applicable", 0, 1)), 0)
Explanation...
Take the sum of the status values...("Not Met" = 0)
IF([KPI  A]@row = "Partially Met", 0.5, IF([KPI  A]@row = "Met", 1, 0)) IF([KPI  B]@row = "Partially Met", 0.5, IF([KPI  A]@row = "Met", 1, 0)) IF([KPI  C]@row = "Partially Met", 0.5, IF([KPI  A]@row = "Met", 1, 0))
...and divide by the sum of the applicable statuses...
IF([KPI  A]@row = "Not Applicable",0,1) IF([KPI  B]@row = "Not Applicable",0,1) IF([KPI  C]@row = "Not Applicable",0,1)
...wrap it all inside of IFERROR() to catch the potential dividebyzero error.
If you want to account for when any of the columns is blank, the use...
IF(OR([KPI  A]@row ="", [KPI  B]@row="", [KPI  C]@row=""), "", IFERROR(expression_above))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!