Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper Contributor

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 of 100% or more

4 Replies
best response confirmed by Johnny_B1640 (Copper Contributor)
Solution

@Johnny_B1640 

 

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

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.

@Johnny_B1640 

That'd be complicated, I fear.

@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.

1 best response

Accepted Solutions
best response confirmed by Johnny_B1640 (Copper Contributor)
Solution

@Johnny_B1640 

 

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

View solution in original post