Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
ASSIGN VALUE TO CHECKBOX AND CALCULATE AVERAGE?
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
ASSIGN VALUE TO CHECKBOX AND CALCULATE AVERAGE?
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,MA TCH(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 --- "nickee" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
ASSIGN VALUE TO CHECKBOX AND CALCULATE AVERAGE?
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "nickee" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate Average | Excel Discussion (Misc queries) | |||
How to Calculate Average | Excel Discussion (Misc queries) | |||
How do I calculate an average if | Excel Discussion (Misc queries) | |||
CALCULATE WITH AVERAGE ???? | Excel Discussion (Misc queries) | |||
How do I assign a numerical value to a textual entry to calculate percent completion? | Excel Worksheet Functions |