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

  • CodyRussell
    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
    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
    Toufong Vang ✭✭✭✭✭
    edited 02/03/24

    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!