Link to home
Start Free TrialLog in
Avatar of happy 1001
happy 1001

asked on

Best, Average or Worst ?

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 -
User generated image
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
Avatar of D Patel
D Patel
Flag of India image

This can be done by using Stacked Bar Chart...
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...
User generated imageUser generated imageUser generated imageUser generated imageUser generated imageUser generated imageUser generated image
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
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
Avatar of d-glitch
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:
User generated image
Avatar of happy 1001
happy 1001

ASKER

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
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?
SOLUTION
Avatar of D Patel
D Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Thank you so much John. You did even more then what I asked for. Excellent Work. Very Impressive.

Best Regards