# AVG(IF) statement based on criteria

Options
✭✭✭✭✭✭

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
edited 02/03/24
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!