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 divide-by-zero 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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!