Tracking Call Centre Metrics as a Race

K

kwelly

New Member
Joined
Aug 20, 2010
Messages
3
I'm not sure if this can be done, but I work in a call center and we run a race with my team where for every 9 ptp (promise to pay) they get 10 points and if they get a 100% call score they get a bonus 10 points. and if they win a quiz they wiin 10 points.

We usually just have a white board and I manually update it and draw a line from each name from start line to finish.

We have 15 agents and finish line is 250 points...we also have a quick bonus first to 50 and first to 100.

I would like to create an Excel spreadsheet where the agents are all listed down the first column A and each cell will represent 10 points going across.

I would like to then have on a seperate page or hidden in column BB all the Agents names and next to that I can type in the number of ptps, and another 2 columns marking either 1, 2, 3 etc for their 100% call scores and quiz wins.

Now the hard part I want it to automatically calculate and then for every 10 points earned that next to their name it would change the colour of the cell and move towards the magic finish line at 250 but also have a formula at the 50 point line and 100 point line that for the person who first got there the cell changed to a different colour.

Then I could share the excel book on our shared drive and the agents can check each day/week to look at their progress in the week.

any ideas...or is this just a dream?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Yo would need some code to timestamp the first person to reach 50 / 100 for the extra colour, but the 10 points per cell can be done with a conditional format.

Apply a conditional format to your range of 25 columns x 15 rows using the formula
=(COLUMN(A1)*10)<=$BB1

Note that the row number in the formula should match the first row of entries / coloured cells.
 
Upvote 0
Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Agents</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">10 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">20 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">30 pts</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Bertie</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">20</TD><TD style="TEXT-ALIGN: center">25</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF($B$101<10,$B$101,10)</TD></TR><TR><TD>C2</TD><TD>=IF($B$101<20,$B$101,20)</TD></TR><TR><TD>D2</TD><TD>=IF($B$101<30,$B$101,30)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>




In the example above I have used a split screen on row 100 to give a control panel at the bottom of the page. You can use password protection to prevent agents from 'amending' their scores.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">100</TD><TD>Agents</TD><TD>Pts</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">101</TD><TD>Bertie</TD><TD style="TEXT-ALIGN: right">25</TD></TR></TBODY></TABLE>




As Jason said you can use conditional formatting to change the colour of each cell. You only need to enter the formula and formatting for the first row and drag it down.
 
Upvote 0
Oops! Sorry, I didn't see the part about 50 and 100.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"><COL style="WIDTH: 50px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Agents</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">1st 50</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">1st 100</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">10 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">20 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">30 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">40 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">50 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">60 pts</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Bertie</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">40</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #339966; COLOR: #ffffff">50</TD><TD style="TEXT-ALIGN: center">51</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=C101</TD></TR><TR><TD>C2</TD><TD>=D101</TD></TR><TR><TD>D2</TD><TD>=IF($B$101<10,$B$101,10)</TD></TR><TR><TD>E2</TD><TD>=IF($B$101<20,$B$101,20)</TD></TR><TR><TD>F2</TD><TD>=IF($B$101<30,$B$101,30)</TD></TR><TR><TD>G2</TD><TD>=IF($B$101<40,$B$101,40)</TD></TR><TR><TD>H2</TD><TD>=IF($B$101<50,$B$101,50)</TD></TR><TR><TD>I2</TD><TD>=IF($B$101<60,$B$101,60)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


The Control Panel
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"><COL style="WIDTH: 50px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">100</TD><TD>Agents</TD><TD>Pts</TD><TD>50pts</TD><TD>100pts</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">101</TD><TD>Bertie</TD><TD style="TEXT-ALIGN: right">51</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>

The Conditional Formatting B2 and C2
Click Format => Conditional Formatting
Condition1 CellValue = 1, Format accordingly
Condition2 CellValue = 2, Format accordingly
Condition3 CellValue = 3, Format accordingly
 
Upvote 0
thanks...that works a treat...can you please tell me though what i need to enter to change the cell colour automatically once 10, 20, 30 etc is reached and how do I hide the value from showing in the cell so only either blank cell or the colour is showing.

Cheers
Kelly
 
Upvote 0
If you mean keep the cells blank until a value is reached then here is the amended formula

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"><COL style="WIDTH: 50px"><COL style="WIDTH: 47px"><COL style="WIDTH: 47px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Agents</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">1st 50</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">1st 100</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Quiz</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Final</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">10 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">20 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">30 pts</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #333399; COLOR: #ffffff">40 pts</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; COLOR: #800000">Bertie</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">20</TD><TD style="TEXT-ALIGN: center">22</TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B5</TD><TD>=B25</TD></TR><TR><TD>C5</TD><TD>=IF(D25=1,1,"")</TD></TR><TR><TD>D5</TD><TD>=IF(E25=1,1,"")</TD></TR><TR><TD>E5</TD><TD>=IF(F25=1,1,"")</TD></TR><TR><TD>G5</TD><TD>=IF($C25<10,$C25,10)</TD></TR><TR><TD>H5</TD><TD>=IF($C25<10,"",IF($C25<20,$C25,20))</TD></TR><TR><TD>I5</TD><TD>=IF($C25<20,"",IF($C25<30,$C25,30))</TD></TR><TR><TD>J5</TD><TD>=IF($C25<30,"",IF($C25<40,$C25,40))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


I am afraid I have changed the ranges from earlier once I realised that the sheet can be locked for editing, see the help file for how to do this.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 43px"><COL style="WIDTH: 50px"><COL style="WIDTH: 47px"><COL style="WIDTH: 47px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Agents</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Pts</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">50pts</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">100pts</TD><TD style="BACKGROUND-COLOR: #333399; COLOR: #ffffff">Quiz</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: left; COLOR: #800000">Bertie</TD><TD style="TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD></TD></TR></TBODY></TABLE>




The Formula and conditional format will have to be entered for the first row containing an agent, row 5. Then once you drag the formula over the range the formatting will be copied with it.

Conditional Formatting
Click in cell G5
Click Format => Conditional Formatting
set: Cell value, equals, type in 10
Click on the Format button
Set the colour of the font and the colour of the 'patterns', i.e., background.
If you want to hide the numbers once a value has been reached set the font and background to the same colour.

Click Format => Conditional Formatting
H5: Cell value, equals to 20, set the colours
I5: Cell value, equals to 30, set the colours
etc.,
 
Upvote 0
kwelly said:
thanks...that works a treat...can you please tell me though what i need to enter to change the cell colour automatically once 10, 20, 30 etc is reached and how do I hide the value from showing in the cell so only either blank cell or the colour is showing.

Cheers
Kelly
Click to expand...


Thats what the conditional format I suggested will do, I had columns B:Z changing colour based on the value BB

I'll upload a sheet with this already done later in case the original suggestion didn't make much sense.

If you could make do with listing the names of the first to 50, etc in day-glo fonts, then maybe aa horizontal bar chart would be a better way to display progress.
 
Upvote 0
Slightly off topic.
I know that a team works better when their immediate supervisor is looking at them, evaluating them and encouraging them.
Does one get the same results when it is a spreadsheet that does the evaluating and encouraging?


The old school asks, at what point a supervisor using a computer as a tool becomes supervision by computer. And how does passing that point change the nature and results of that supervision.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Complex tiebreaker
Replies
7
Views
350
HorizonHunter
H
K
  • Solved
Sports Carnival Scoring
Replies
4
Views
501
fjns
F
H
  • Question
Need a button function to share a workbook with colleague
Replies
0
Views
326
HMC62015
H
G
  • Question
Links to cells in other Workbooks occasionally change the WORKSHEET reference. Why?
2
Replies
11
Views
177
GTS
G
P
  • Solved
Cell Name change based on another cell BG Colour
2 3
Replies
20
Views
1K
PeterOz
P

Forum statistics

Threads
1,216,172
Messages
6,129,291
Members
449,498
Latest member
Lee_ray

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top