Solved

Best, Average or Worst ?

Posted on 2016-09-29
12
67 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 5

Expert Comment

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

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 5

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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now