Forum Discussion

Johnny_B1640's avatar
Johnny_B1640
Copper Contributor
Dec 24, 2023
Solved

Is there a formula that lists the agents with an average of 100%

I have data that has all of the grades of my team. Members will have multiple entries in excel as we're graded per transaction. I just need a formula that lists all members that have an average score...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 24, 2023

    Johnny_B1640 

     

    =UNIQUE(FILTER(Name_Range,AVERAGEIFS(Grade_Range,Name_Range,Name_Range)>=100%,""))

HansVogelaar's avatar
HansVogelaar
MVP
Dec 24, 2023

Johnny_B1640 

 

=UNIQUE(FILTER(Name_Range,AVERAGEIFS(Grade_Range,Name_Range,Name_Range)>=100%,""))

  • Johnny_B1640's avatar
    Johnny_B1640
    Copper Contributor
    Dec 26, 2023
    You're a lifesaver! This worked perfectly. One more question tho: I'm planning to make a simple dashboard for simple presentation that doesn't require much tinkering other than adding more raw data. That said, is there a way for the formula to ignore the blank cells? I tried using whole columns instead of just specific ranges that contain data and it's just returning 0.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 27, 2023

      Johnny_B1640 

      If the range with non-empty cells is contiguous, you can define dynamic named ranges and use those in the formula. Or convert the range to a table; add new data to the table and refer to the table columns in the formula.

      I have attached a demo workbook with both approaches.

Resources