I am working on a spreadsheet that charts the results of our fishing club tournaments. I would like to discover a way to automatically show who is winning the tournament as I enter the results. Your place in the standings is based on total weight of the fish you caught. Person with the highest weight wins. Thank you.
Last edited by allnet000; 12-29-2009 at 05:45 PM.
Hi allnet,
welcome to the forum.
If your results are calculated in column A, you can use a formula like
=max(A1:A100)
in another cell to display the highest value. Then use a vlookup to display the name next to it.
Other options are available to show for example the three highest scores:
=LARGE($A$1:$A$100,1)
=LARGE($A$1:$A$100,2)
=LARGE($A$1:$A$100,3)
or with RANK() you can show which rank a particular score has in the overall picture
=RANK(A1,$A$1:$A$100)
Feel welcome to upload a data sample if you need help implementing this.
cheers
Another option: Use a Pivot Table. After entering the data, you only need to right-click to refresh it, plus you can manipulate the data and sort it.
Attached is a basic table. Post a sample workbook if you need help. Pivot Tables are best based on source data, if the database is large, that is defined by a dynamic named range.
You might also consider using Conditional Formatting in the data table, if applicable, to automatically highlight the winner.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Palmetto,
Thank you for the help. I am not sure how to make pivot table work for what I am trying to accomplish.
I am attaching a sample of the spreadsheet I have started. The goal is to have the Leader Board section (line 83) to automatically update who is in first, second, etc place as I enter in the weights. This would be for both the boater and co-angler division. For the lunker section it would just be a conditional format that highlights the leader.
I am using excel 2003 and a PC user. I know...I know.
Thanks again! Let me know if you have additional suggestions.
Maybe something along these lines :
Book2(1).xls
Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
Hi,
see what you can do with the attached. I've put in some real names, since the Name 1 did lead to confusion in the leader board. I hope this makes it clearer.
I've included a tie breaker in your formula in columns R and S
=RANK(M4,$M$4:$M$76)+COUNTIF($M$4:M4,M4)-1
so you won't end up with two people having the same rank, since that would result in a rank number missing and an #NA! error in the leader board.
I've used a simple INDEX/MATCH to lookup the names for the ranks.
Hope you can follow this.
Perfect! Thank you all for your help! Awesome
Last edited by allnet000; 12-29-2009 at 05:44 PM. Reason: PROBLEM SOLVED
Thank you for the help in my question on Automatic Sorting of Event Winners. Is there a way for me to adjust the tie breaker to the number of fish caught? So that way if two people have the same weight, it will then look at the number of fish caught to determine who comes in a higher place?
Chip Pearson has the tie breaker approach here
http://www.cpearson.com/excel/Rank.aspx
We just started a feeder fishing club in a small town in Bulgaria.
But no one of us is good with Excel.
Can I request a working xls file that manages the catch in kg and penalty points?
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks