?
Solved

Best, Average or Worst ?

Posted on 2016-09-29
12
Medium Priority
?
114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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 1600 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

741 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