SOLVED

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

Copper Contributor

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

 

Example.png

3 Replies

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

best response confirmed by HansVogelaar (MVP)
Solution

@jani96 

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

average time.png

Thank you Oliver! Much appreciate the help
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@jani96 

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

average time.png

View solution in original post