Help Designing Quality Spreadsheet

[Brent Sweet]

Hello!

I need to design a spreadsheet that I believe is a little over my head. I
have a list of quality scores from a call center as follows:

Agent Score Supervisor
Bill Smith 89 Jack Rodgers
Tom Jones 95 Jack Rodgers
Bill Smith 95 Jack Rodgers
Willy North 99 Paul Jones
Eric South 92 Paul Jones

I have this list on sheet 2. What I need is sheet 1 to have a box basically
for each supervisor (12 supervisors). Each supervisor has about 20 agents.
Then I want it to index the scores for each individual on that supervisors
team, do a count of how many scores they have and an average. The final
Display would look something like this

Supervisor: Jack Rodgers
Agent Count Average 1 2 3 4 5 6 7 8 9 10
11 12
Bill Smith 2 92 89 95
Tom Jones 1 95 95



Supervisor: Paul Jones
Agent Count Average 1 2 3 4 5 6 7 8 9 10
11 12
Willy North 1 99 99
Eric South 1 92 92


Can someone please help me get started with this? I have no idea how to get
excel to do these lookups.

Thanks,
Brent Sweet

 

[Pete_UK]

First of all, assuming your list in Sheet2 occupies columns A to C
with headers in row 1, put the header "Unique_ref" in D1 and this
formula in D2:

=A2&"_"&COUNTIF(A$2:A2,A2)

Copy this formula down column D for as many entries as you have in the
other rows - it will give you something like Bill Smith_1, Tom
Jones_1, Bill Smith_2 etc, i.e. it will uniquely identify each entry
in the table by adding a sequential number to each name.

Then in Sheet 1 put these formulae in the cells stated:

B3: =COUNT(D3:O3)
C3: =IF(B3=0,0,AVERAGE(D3:O3))
D3: =IF(ISNA(MATCH($A3&"_"&D$2,Sheet2!$D:$D,0)),"",INDEX(Sheet2!$B:
$B,MATCH($A3&"_"&D$2,Sheet2!$D:$D,0)))

I have assumed here that you have the Supervisor in row 1 and then the
sub heading beginning with Agent in row 2, so that the names begin in
A3. Copy the formula from D3 across into the range E3:O3.

Then copy the formulae from B3:O3 down for as many rows as the first
supervisor has agents. This same range can be copied into the
appropriate cells of the block for the next supervisor, and so on for
each supervisor.

I have assumed that you will compile the list of agents under each
supervisor in Sheet1 manually.

Hope this helps.

Pete

 

[Herbert Seidenberg]

Pivot Table + one formula:
=COUNTIF($A$2:A2,Agent)
http://www.freefilehosting.net/download/3cem5

 

[Eli]

Hello Brent,
To analyse large Excels that grow on a daily/periodically basis and have to
be split into different views by different criteria it is better to use BI
tools that convert the Excel flat file into a multidemensional model (like
OLAP) which you can then easily analyse.

Such a BI tool is Prism (www.sisense.com) that connects to Excel (as well as
to SQL Server, OLAP, MySQL ant others), and then automates the whole process
for preparing views and reports.
In your particular case, Prism will enable you to create for each supervisor
and agent a Scores Dashboard as well as comparison charts between agents and
supervisors.

The output can look as follows:

Jack Rodgers
Agent Score Count Score
Eric South 92.0 1
Willy North 99.0 1

Paul Jones
Agent Score Count Score
Bill Smith 92.0 2
Tom Jones 95.0 1


Supervisor Score
Jack Rodgers 93.0
Paul Jones 95.5



Regards

Eli