Excel 2007 : Determining which action had highest average score

[dip11]

Hi,

I hope you can help me figure out how to do this. I want Excel to pull the text string that had the highest associated average score for a certain actor and display it. Sound simple? Well it isn't I think

So here's the scenario:

• 50 people assigned an ID 1-50
• 1500+ different actions which are text
• Satisfaction with an action ranked 0-10

What I would like to do is this:

1. Reference a cell which contains the ID (1-50) for a given actor (already done)
2. Then average all satisfaction scores per action for this actor (i.e. Jumping has 3 entries for the actor, rated 0, 6, 9, average that keeping in mind there's a host of different actions)
3. Return the name of the action with the highest average in cell A
4. Return the average value of the action in cell B
5. Return the number of this action counted in cell C

Repeat for the next 4 highest averages top create a top5 highest average list of actions.

Please let me know if I need to clarify this further!

P.S.:
ID (actor reference) is already given on the current worksheet. It references AS:AS of Sheet1. The ID can be changed on the current worksheet to produce a report for different actors on the current worksheet. This is already in place.
Action is found on D:D of Sheet1
Satisfaction score is found on H:H of Sheet1

ReportTest2.xlsx

[dip11]

Some further thought on this problem:

cell B and cell C I actually know how to code:
B: =AVERAGEIFS(Sheet1!H:H,Sheet1!D:D,CELL-A,Sheet1!AS:AS,J2)

Where CELL-A is the result of cell A and J2 is the ID reference on the output sheet.

C: =COUNTIFS(Sheet1!H:H,ISNUMBER,Sheet1!D:D,CELL-A,Sheet1!AS:AS,J2)

Though I am not quite sure what argument I would use for the brackets of ISNUMBER

Problem is just how do I determine cell A?

Cell A = Action (text) from D:D that has the highest average score from H:H for Actor AS:AS

I realize a Pivot Table will show cell A for me, but I would have to manually extract the top5 text for each actor every month which would be a lot of work, especially as the list of possible actions grows each month, so I cannot reference a set cell in a pivot table.


Now with example Book:
ReportTest2.xlsx

[dip11]

I think the only way to do this at the moment would be to have a separate spreadsheet with every single action as rows, all the actors as columns and the fillers as the average scores and then draw out the highest average from there?

My only concern is as the list of actions is growing every month, the table would need to be updated every month rather than draw the information directly from the main data sheet.

Anyone?