ASSIGN VALUE TO CHECKBOX AND CALCULATE AVERAGE?

[nickee]

I have been tasked to make a new quarterly performance evaluation for our
call center. There are 5 ratings (Exceptional, Exceeds, Meets, etc) per
quarter (4 quarters per year). What I would like to figure out is how to
assign a value to each checkbox for each quarter. For example, if the
checkbox for 1st quarter is checked next to exceptional, I'd like the value
to be 5. If the checkbox is checked next to exceeds, I'd like the value to
be 4, and so on down the line.

Then, after all 4 quarters have been evaluated, I'd like a "final score" to
be calculated. So, let's say first quarter the rep got a meets - 3 (cell
B6), 2nd quarter meets - 3 (cell C6), 3rd quarter exceeds - 4 (cell D5) and
4th quarter exceptional - 5 (cell E4)...so their final score for the year
would be 3.75.

I hope this makes sense and somehow I doubt it's even possible, but I
thought I would ask.

 

[Max]

Here's an easier alternative using DVs to rate and a formula to compute the
score which should satisfy the core intents

Illustrated in this sample:
http://freefilehosting.net/download/43mbd
Performance Rating Template.xls

Construct:
Rep names listed in A2 down
Ratings for each rep per Quarter are selected via DVs in B2:E2 down

A rating table is created in H2:I5, viz:
Exceptional 5
Exceeds 4
Meets 3
Below 2
Poor 1

Average Scores are then calculated in F2 down
In F2, copied down
=IF(COUNTA(B2:E2)<4,"",SUMPRODUCT(N(OFFSET($I$1,MATCH(B2:E2,$H$2:$H$6,0),))/4))

p/s: Do not use all caps, not even in the subject line
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Shane Devenshire]

Hi,

If you have a lot of these already set up you need to link them to cells and
then use an IF statement to return the desired result.

For example, suppose A1 contains a checkbox of Exceptional. Right-click the
checkbox and choose Format Control, Control tab, in the Cell link box enter
N1 (or any cell you want.
In cell B1 or where ever you want the resulting number enter
=IF(N1=TRUE,5,"")

This is if your checkbox came from the Forms toolbar, if not (then you used
the Control Toolbox). Select the checkbox, click the Design Mode button and
then the Properties button. In the LindedCell property enter N1 and preceed
as above.