150 cars and finding the top 10

[DMHutch]

I need help

We have 150 car entries.

I need a formula to calculate the top 10 cars.

Top 2 cars  in category Classics

Top 2 cars in category STOCK

Top 2 cars in category RODS/MODIFIED

Top 2 cars in category SPECIAL INTEREST

Top 2 cars in category PICKUPS

I have used Excel many times but this is not something I have done before.  I really need your expertise.  It is urgent as the show is on 17th Sept 2022.  This was dropped on me and I am stressing.  Please help.  

[#2]

@DMHutch 

Love to help, but you need to help us help you.

What is the basis for determining "Top"? Are there votes for each of the entries? Some other quantitative or qualitative ratings associated with each of the 150 entries. 

In other words, what's the nature of the database that has been dumped on you? 

Importantly, is it something you can share? Or, if the actual database is private (at least until the time comes to reveal those winners) could you at least post a sample with actual identifying details blocked off, but with all of the fields that would be used to determine "top"? You are not likely to be able to post that file here in the forum but you can post it on any of the cloud services (OneDrive, Google Drive, or something else in that category), and then post a link here, and make sure you've granted access.

In the absence though of your answering some of the questions I've asked, there's not a lot of help that can be given. You need to give us an idea of what you're working with.

[#3]

@mathetes 

Thank you for your response.

People vote on their favorite Classic car.  The car is numbered 100.

Each time the car is voted on it is counted.  The car with the most votes wins that category.

No names are listed.  Based on the car entry number and how many votes it gets in that category.  There are 5 categories as listed before and each category has 2 winners determined by the number of votes.  Total of 10 cars winning.  

[#4]

Share a sample workbook and show your desired output.

[#5]

@DMHutch Hey DM, I'm attaching a very basic template that will hopefully get what you need.  If you copy your data into the table with Category/Entry/Vote Tally (columns B:D), the formulas will expand to include all the entries.  It doesn't matter if you need to copy more data than what's there as long as the end result contains the three headers mentioned above. 

Then there's a summary to the right of the table that ranks the cars using most votes.  You'll need to determine how to break the tie if you have 3 cars in a given category all with the same number of winning votes.  

Hope this helps get you started,

Dexter

[#6]

@DMราคารถมือสอง

Here's how you can calculate the top 10 cars from your list of 150 cars in Excel:

1. First, make sure your data is organized in columns with headers such as "Category" (Classics, Stock, Rods/Modified, Special Interest, Pickups) and "Car Name" (the name of each car).

2. Assuming your data starts from cell A2 (A1 being the header for "Category"), you can use the following formula to rank the cars within each category:

   • For Classics (assuming "Classics" is in column A and car names in column B):

     excel

     =IF(A2="Classics", RANK(B2, B$2:B$151, 0), "")

   • For Stock (assuming "Stock" is in column A and car names in column B):

     excel

     =IF(A2="Stock", RANK(B2, B$2:B$151, 0), "")

   • Repeat the same pattern for Rods/Modified, Special Interest, and Pickups.

3. Drag the formula down for all 150 cars.

4. Now, you'll have rankings for each category. Next, you can use the LARGE function to find the top 2 cars in each category. For example, for Classics:

   excel

   =LARGE(range_for_classics_rankings, 1) =LARGE(range_for_classics_rankings, 2)

   Replace "range_for_classics_rankings" with the actual range of rankings for Classics.

5. Repeat the LARGE function for Stock, Rods/Modified, Special Interest, and Pickups to find the top 2 cars in each category.

6. Finally, combine all the results to get the top 10 cars overall.

This process should give you the top 10 cars based on their rankings within each category. Let me know if you need further clarification or assistance with any step!