• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to filter the users by year

mhghg

Member
Hello All,
I am creating a dashboard for cocurricular performance. I have finished the first part, but
I am stucked with the second part.

The second part is to list the top/bottom 5 performers when the user select the year from the drop down list on the top left corner for the sheet. I have created the format but I do not know how to filter it. Anyone helps please
here is the link to the file:

https://drive.google.com/file/d/0B0rMYzlRxO33TURYWWlsNXgwTEU/view?usp=sharing

Thanks,
Mhg
 
Hi:
Could you manually give the table with top/bottom performer in you dash board tab and specify which columns of data it is referring to, so that a logical formula can be build in.

Thanks
 
Hi Nebu,
I am not quite sure what you mean but I try to explain
1. I have added the border to the section when i need the data to be sorted
2. That section referred to data in Calculation columns S26 to W26 as the user is repeated some years so I have to created user unique name. Then I sorted the data according to the activity that they involve.But I do not know how to filter out by year so it wont display 0 in that section when you click the radio button.
3. I try to use conditional formatting in column X5 of my calculation to highlight top 10% and bottom 10% but so far it does not work well logically
https://drive.google.com/open?id=0B...FtemFIOEZXWmdVYzRiNE1rLVRvVl81Yy1CcEp4c1g1NDA
https://drive.google.com/open?id=0B...FtemFIOEZXWmdVYzRiNE1rLVRvVl81Yy1CcEp4c1g1NDA
 
Hi:

I guess you will have to decide on what basis you want to identify the top and bottom 10, you have five categories and an individual can come top for 1 category and bottom for the rest of the categories, there can be so many permutations and combinations like this. Hence, you have to decide which category will take precedence while deciding on the top or bottom 10, or do you want to use a combination of all the categories to decide (something like assigning weights to the categories). Right now it is sorting the names just based on the logic you have used in your unique order column by adding and multiplying certain values to the unique serial number assigned .

Thanks
 
Hi Nebu,
May be I wish it could be like this
1. Filter out the main data when user select it by year
2. Then create different sorted data table for each year
3. Highlight only top 5 highest scores and bottom 5 lowest scores for each category using conditional format after sorted.

What u think?
 
Hi:

The problem is that you have so many individuals with the same points / category combinations for a particular year. It will be really difficult to rank them based on the scores, you may have come up with some other scoring system to do this. After you decided on this it is rather easy to identify top & bottom 5' based on the combination of index and small if/large if formula.

Thanks
 
Hi Nebu,
Thank you for your idea. I work out the scoring system like this:
1. Any user participates at least 3 activities in 3 different categories will qualify for the top performer list.
2. If no one qualify for the above condition then any user participate at least 2 activities in 2 different categories will be the next choice.

Is that ok with this Nebu?
 
Hi:

It will be alright if you can quantify this logic and put a number around it.

Thanks
 
Hi:

Please find the attached. I have put some formulas to arrive at top 5 and bottom 5 , please go through it and let me know whether this looks correct.

Thanks
 

Attachments

  • Cocurricular.xlsb
    937.8 KB · Views: 11
Thanks Nebu, I try to get my head around your ranking logic. And it seems to me that my sorted users list is not working properly when I compare your result with the list. Give me some time to think :)
 
Hi:

Please find the attached. I have put some formulas to arrive at top 5 and bottom 5 , please go through it and let me know whether this looks correct.

Thanks
Hi Nebu,
It looks fantastic to me. I did not how you can come up with the ranking logic like that. It is so great. I like to know how do you create the list of the unique name and also in the dashboard is there any way that we do not let the user see the formula ?
 
Hi:

Th ranking logic is simple here is the explanation:

1. I counted the no of different events an individual has attended, say Mr.X had attended FA/SJ,DB/PS and Other then the count is 3.
2. Then I summed the no of times an individual has attended a particular event, Say Mr.x has attended FA/SJ- 3 times,DB/PS-2 times and Other-1 time, here the sum is 6.
3. Then I concatenated the no of events attend and no of time events attended in our example it is 3 and 6 which will give you 36.
4. I added a very small amount to this combination to make the ranks unique, the same logic you have used.
Since the combination of no of events attended and the no of times attended will have a logical flow it will end up giving you top 5 or bottom five if you use excel's native function Large or Small respectively.

Hope I make sense.

For hiding the formulas in the dashboard sheet you will have to protect the sheet, here is the link how to do that.

https://support.office.com/en-us/ar...formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Thanks
 
Hi Nebu,
I understand your logic completely, but I just wonder how you create the list of user name in data/column P.
Thank you very much for your time, I really appreciate it.
 
Hi:

Column P is easy to achieve, copy the Name column and paste it in column P select the entire column P and go to the data option on the ribbon , select remove duplicates excel will remove all the duplicates and will give you unique values.

Thanks
 
Back
Top