Solved

Best, Average or Worst ?

Posted on 2016-09-29
12
93 Views
Last Modified: 2016-10-03
Hi Experts

I have some data in excel, which represent the % Performance of 5 different teams over past one year. I am trying to figure out a method by which I can classify the % Performance into 5 different categories, as follows -

Best Performance
Good Performance
Average Performance
Weak Performance
Worst Performance

I have tried to visually represent the same idea in this image -
Best-or-Worst.png
Suppose, for some date, Team A had performance of -8 % and Team B had performance of 22 %, then how would I tell if it is best, average or worst performance for those respective teams ?

Can you please suggest some ideas which can be used for such classification ? How would you DEFINE Best, Average and Worst Performance etc. ? Which method to use for such work ?


I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data -
Best-or-Worst---Classification.xlsx


Thanks and regards
0
Comment
Question by:happy 1001
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 7

Expert Comment

by:D Patel
ID: 41822968
This can be done by using Stacked Bar Chart...
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41823029
Do you have any idea on Analysis ToolPack?


It is Excel Add-ins that can be enabled using Options dialog.

To know more about it follow the below screenshots...
OptionsAdd-InsAnalysis ToolPackData Analysis from RibbonRank And PercentileInputOutput
After performing this you will need to sort the data based on ranking, then will need to obtain percentile. Finally, you will have to draw Thermometer Chart to get the desired output.

Let me know whether it helps or not.

Regards,
D Patel
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41823038
Hi Happy 1001,

Based on which criteria you want to give ranking.

Does it based on total of all the 5 teams or what?

Please clarify.

Regards,
D Patel
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 27

Expert Comment

by:d-glitch
ID: 41823515
With five teams and five categories, why not just rank them?

A thermometer chart for a years worth of data is sort of underwhelming.
I really like this 5-team chart that also covers a seasons worth of data:
Five Teams
0
 

Author Comment

by:happy 1001
ID: 41823588
Thank you experts.

I am myself not sure which METHOD should be used for this work, thats why I asked for it in the original post.

The ranking needs to be given based on Each Team Results SEPARATELY, based on total data for that team. It is in no way to be related to the scores of the other teams. Every team will have their own separate thermometer. And a score that qualifies as "Best Performance" for one team, might qualify as "Average Performance" for other team. Hopefully you understand the point now.

And please note that my current emphasis is on finding the APPROPRIATE METHOD for dividing the values into those 5 CATEGORIES. How Exactly to do that ? This is my Main Question.

The VISUALIZATION Part is Secondary. Because first we need to prepare the data for those five categories, only then we can think about visualizing it.

If any more doubts, then please ask.

Thanks a lot
0
 
LVL 27

Expert Comment

by:d-glitch
ID: 41823657
The ranking needs to be given based on Each Team Results SEPARATELY, based on total data for that team.
That means that there is no ranking.  You could have 5 Average teams, 5 Best teams, or 5 Worst teams.  And a whole years worth of data is to be condensed into one qualitative phrase.

Every/most business day, each team gets a number.  
What does the number mean?  How do you determine if it is good or bad?  
Who decides what is a perfect score for any particular day and team?  Does it change over the course of the year?
0
 
LVL 7

Assisted Solution

by:D Patel
D Patel earned 100 total points
ID: 41823831
Hi Happy 1001,

First of all you will need to find out the percentage daywise for individual teams.

i.e. Total = A + B + C + D + E
       Percentage of Individual = A / Total

Once calculated do the following :

Percentage	                Performance	       Frequency	Percentage
Above 90	                        Best	                       9	                0.72%
Above 70 and Below 90	Good	               9	                0.72%
Above 40 and Below 70	Average	              30	                2.40%
Above 20 AND Below 40	Weak	              111	                8.88%
Below 20	                        Worst	              1091	                87.28%

Open in new window


To calculate frequency use =COUNTIFS()
Then calculate Percentage Frequency / 1250 (i.e. total occurances)

Then draw the chart...
Like that....
0
 
LVL 2

Expert Comment

by:John Fistere
ID: 41824987
Let me understand the question better. I believe we are consider each team's scores independently. Is the question 1) How many of the team's scores fall in the top 5%, 20%, 50%, 20%, and 5%, (for example) of that teams range? or 2) What is the range of scores for the top 5%, 20%, 50%, 20%, and 5% of team performances? --- Or is it something else?

John
0
 

Author Comment

by:happy 1001
ID: 41825127
Thank you for your comments Experts.

I am sorry for any confusions. For making it simpler, please forget about all other teams and just focus on the values for the TEAM A ONLY.

Now, I am looking for ways in which I can DEFINE Best, Average and Worst Performance etc. How to define such SLABS ? What are the various methods for doing such work ? for example, one method is already suggested by D Patel, having 5 slabs from Below 20 ---- to ---- Above 90

What are other possible methods for defining such slabs ? Once I am aware of 4-5 such methods for defining the slabs, then I can choose, which one will suite my needs best. First part is to define the Slabs. I hope you guys understand it now.

Thanks and regards
0
 
LVL 2

Accepted Solution

by:
John Fistere earned 400 total points
ID: 41825621
0
 
LVL 2

Expert Comment

by:John Fistere
ID: 41825629
Well, I got interested in your project and did a little Excel work and came up with the attached file. The part I have added is between columns E and I. The only data to be manually entered are the boundary lines percentages between the "slabs" in cells E4 to E7. This will give you five categories of scores with the boundary scores given in column F, the count of scores in each "slab" in column G, and the size of each slab in percent, in column I.

What you will find is that the scores are highly concentrated in one range so that entering typical 5, 25,75, 95 percent values will not be particularly informative. I have entered different percentages to spread out the counts a little bit.

Have fun.
0
 

Author Comment

by:happy 1001
ID: 41826322
Thank you so much John. You did even more then what I asked for. Excellent Work. Very Impressive.

Best Regards
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article provides a brief introduction to tissue engineering, the process by which organs can be grown artificially. It covers the problems with organ transplants, the tissue engineering process, and the current successes and problems of the tec…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question