SOLVED

# Calculating then sorting by Highest to Lowest Average for Case Data in Excel

Copper Contributor

# Calculating then sorting by Highest to Lowest Average for Case Data in Excel

Hi!

I'm hoping someone may be able to assist with something I am trying to achieve in excel with regards to calculating the average case resolution time on case data for a set of customers, so I can then sort my spreadsheet from highest to lowest to identify which customer's cases are taking the longest to resolve.

I've attached a very simplified example of what the spreadsheet looks like - but the actual data I'm working with contains several hundred customers with over 4000 cases (rows) meaning I need to find a way to automate this through a formula of some sort.

Any help would be appreciated :)

3 Replies

# Re: Calculating then sorting by Highest to Lowest Average for Case Data in Excel

@jani96 I would consider a pivot table as demonstrated in the attached file.

best response confirmed by HansVogelaar (MVP)
Solution

# Re: Calculating then sorting by Highest to Lowest Average for Case Data in Excel

=SORT(UNIQUE(HSTACK(A2:A17,B2:B17,MAP(A2:A17,B2:B17,LAMBDA(name,case,AVERAGEIFS(C2:C17,A2:A17,name,B2:B17,case))))),3,-1)

If you have Office 365 you can apply this formula.

# Re: Calculating then sorting by Highest to Lowest Average for Case Data in Excel

Thank you Oliver! Much appreciate the help
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Calculating then sorting by Highest to Lowest Average for Case Data in Excel

=SORT(UNIQUE(HSTACK(A2:A17,B2:B17,MAP(A2:A17,B2:B17,LAMBDA(name,case,AVERAGEIFS(C2:C17,A2:A17,name,B2:B17,case))))),3,-1)

If you have Office 365 you can apply this formula.