AVG(IF) statement based on criteria

Gaurav Chauhan

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.

CodyRussell

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))))

Gaurav Chauhan

Thank you for sharing the same. There are two elements which have to be considered.

1. There are 4 dropdown values. Met (100%), Not Met(0%), Partially met (50%) and Not applicable
2. 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.

Toufong Vang

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))