Excel Spreadsheet Help

I'm going to have to rate you a 9 on Motivation but only a 4 on Competency.

Well I would grade them in more categories, maybe dozen or more, and rate from 1 to 100 in each category, then go by that number....I would like to see potential, intelligence, physical attributes, experience, discipline, aggressiveness, dependability, dedication, effectiveness, attitude, personal habits, stability, goal oriented....then I would weight the numbers according to most important to least important....then I would throw it all out the window and go with the best players, and work to keep them performing at their peak....unless of course I could prove that this system could predict greatness....

Put the zeros together, showing only one quadrant, calculate the length and angle of the diagonal from the origin to the point. A high number is a high number, an angle above 45 degrees is one attribute, below is the other; and the amount above or below 45 is significant.

Give John the raise, Mike the boot, and be done with it. The other two quadrants might be more interesting and/or problematical.

Simplest ranking method would be (competence x willingness) / 100.

Absolutely correct!!! Poor choice of words....

Just looking for a display to assist the thought process.

And it seems to me that there should be a few more "attributes" taken into account when evaluating any employee. I also use a spreadsheet, but strictly based on a point system. However, just like any mathematical tool or program, garbage in equals garbage out.

There is so much more to people than two characteristics. I use at least 16 different "characteristics" and use a weighted score value for each(the more important the trait, the higher the point value), my numbers are just a check on how the supervisor rates each employee. If they don't jive, something is likely amiss.

On the next evaluation....

I doesn't matter what I think, Graphs don't lie John,.... you're not pulling your weight.

Unless you tell us the formula used to get the XL inputs from John and Mike's figures, giving some negative numbers, I don't see how we can help.

Your example doesn't show any negative numbers. Where do they come into it?

The origin is at (5,5).

No, the origin is at (0,0), that is the definition of the origin.

The number lines intersect at (5,5), there are no negative numbers in that chart.

We may be used to number lines crossing at zero, which gives us the four classic quadrants, but there's no law that says the number lines HAVE to gross at the origin every time. The scales are not part of the data, they do not affect the data, they are simply a way to better visualize the data. Here, where the scale is 0-10, where 5 is 'average,' it makes sense to move the number lines to (5,5) to better visualize 'above average' versus 'below average.

To all - this is not the end all to job performance.... I just wanted some way to visualize employees personalties to work with them improve.

Different people respond to different stimuli, I just was trying to do an exercise to figure some people out.

It might be an idea to delegate the entire process to a Human Resources specialist, perhaps?

I think the function you are looking for is a "scatter chart".

Depending on what version of Excel you are using, it should be available on the insert tab under charts.

Why do the axes cross at 5, 5 then, Boss?

Zactly. OP owes us some splanations.

Or si nees som mor tinking

Because he made it so!

The way I understand it he just does not want to assign negative numbers so he spreads it from 1-10. This way anything under 5 is somehow "negative" without having the negative sign in the front.

Claro Boss?

It provides a quadrant breakdown with 4 regions: High competence, High motivation in the upper right; Low competence, High motivation in the upper left; Low competence, Low motivation in the lower left; and High competence, low motivation in the lower right. Makes an easy visual. And the OP wants no negative numbers, so the axes cross at 5, 5.

One of the more famous versions of this is the Eisenhower Decision Matrix.

What I can't find here is the OP's question. What are you asking for/about, Sid_Sidow?

When you get deeper into this assessment business it is likely that you will find more than 2 dimensions in which to measure (age, physical strength, numeracy, foreign languages?) in which case the appropriate Excel chart is the radar plot, in which all the axes fan out from a central point and the shape and area of the plot give a very good idea of the fitness of the individual for the job.

Hi Sid,

Whenever rating systems are used regardless if it is products or people, some sort of weighting system should be incorporated into the assessment. In your simple example of comparing Mike and John in their Competency and Willingness; Mike still gets his butt kicked by John but he may fair better overall if Willingness was esteemed much more important than Competence.

Furthermore there are many factors that make one Competent, for example John is great when he shows up for work but he takes critical days off by calling in sick but when he is here he is awesome. This approach also allows for statements like "John we need you on this team but we need to know ahead of time if you are not going to be at work tomorrow. And showing up on time is more important to the team then the fact that you are a walking calculator, so keep up the good work but please respect the team by showing up when expected". The following link covers some of this stuff but I am sure there are better resources available. Good Luck! Which begs the question: Is it better to be Good or Lucky...?

http://www.dfpni.gov.uk/eag-the-weighting-and-scoring-method

Massey, that is one heck of a link, anyone with thoughts of rating others need to familiarize themselves with the principles covered. A lot of resentment arises from surveys that only seek out the black and white because the surveyor fails at having in-depth knowledge of the subject being analyzed.

Thinking about your data, you may want to be careful about wanting to get inside the heads of the employee. You're intentions may be good, but that is a place you do not want to be for a number of reasons.

Most states have Employee/Worker Rights. If that employee feels you impeded their rights/Privacy, that employee may file a claim against you.

I highly suggest you leave it to the professionals, and by professionals, I mean people who are familiar with employor/employee law such as Human Resources.

And by the way, Human resources are not there for the employee, they are there for the employer.

Hmm. that cross-hair graphic suggests the idea of someone getting fired (shot), I hope you're not showing that to your employees to "improve their motivation" or the outcome might blow in your face.

Briggs-Meyers was done with a questionnaire. Which was given to the patients. It sound like you are going to fill the questions in for them. You say you want to get in to their brains. You can't unless you have some special powers of mind reading. Each has a different capacity in brain function. That's what makes them along with experiences individuals.

What I see is the only one that you trying to assassinate is yourself. Because your grading system when it get out. Will be very detrimental to the team spirit and morale. For one your conclusions are yours alone. Which always will be based on your personal likes and dislikes. No matter how objective you think you can be. Their reactions will be base on their opinion of personal likes and dislikes. Then your screw in trying at least for awhile to make any improvements.

So as a team you fail.

Now if all you want to be is a paper pusher your charts may impress the boss. But your not part of the team. At least not that team. Paper pusher team is up in the front office. Hiding in their little cubicles out of the way.

there are way too many variables......

one is, what if the patient has ADHD, those data points would be skewd...

imo, I see no good will become of this.

It was just an exercise. I am certainly NOT a paper pusher trying to impress someone!

I figured that, that was the reason I kept my offering simple. If you consider only the one quadrant, plot the X and Y attributes for the candidate, record the radial distance from the origin and the angle. A large radial number scores high, the angular distance from the 45 degree radial shows the degree of and direction of imbalance.

(I can't get the superscript to work in Chrome!)

I would agree with Solar Eagle and others about increasing your range of attributes. I wouldn't bother with a graph as a start, however, I would set up a spread sheet and rate them on a 1 to 10 basis with 10 being Superior and 1 being Needs Vast Improvement. First column is Category of Attribute with named Attribute in the 10 rows and 2nd, 3rd column etc being Week or Month Rated. Then do a Sum of each Column and use the numeric as a marking system, 100 being the "Best Possible Employee You Could Have" and 10 being "Find the First Possible Chance to Get Rid of Them". You could then do a Line Chart-Graph of those Sums of the columns to show improvement or decline over the past periods. For any flat liners with a low score you probably need to watch carefully. Any with declining scores should be interviewed to find out why they have lost interest or are dissatisfied. Just chose your attributes wisely and vary as needed based on position and responsibilities being judged.

I would use a "radar" graph (offered by EXCEL) and more "properties". One graph per person with a reference at "5" for all properties scaled from 0 to 10. It has the advantage to allow a visual evolution if a set is correlated with the date.

values over average are >5 and lower <5.