Excel Formula to Decide Winners of Pinewood Derby

[BHopkins]

Any Excel experts out there that can help me with a function/formula, that will assign winners based upon highest scores?

I am preparing for a Pinewood Derby for the young boys at church. I've put together a race schedule where each boy will get to race his car 6 times - 2 times on each of the 3 tracks. From each heat, the 1st place car will get 3 points, 2nd place gets 2 points, and 3rd place gets 1 point.

The scoring spreadsheet that I've put together has a row that will tally and display each car's total score. I would like a formula that will take the results of that column, and assign 1st place to the highest score, 2nd place to the next highest, etc. Anyone recommend what function I want to use?

 

[Leo99]

SORT Function

The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.

exceljet.net

The sort function will do it.

 

[BHopkins]

Perhaps I don't fully understand the Sort function. But when I have used Sort, it will take several values that are in random order, and sort them by value, lowest to highest, or highest to lowest. That isn't exactly what I'm trying to do.

I want to take the highest value in a row, and have it given the value of 1 in another row. The next highest value in the row would be given the value of 2. And so forth. I don't think Sort can do that. Am I wrong?

 

[BHopkins]

I think I've found what I need. It looks like RANK.EQ will do it. Reading up how to write it.

 

[uc50ic4more]

I think a "pivot table" might be what you are after:

Create a PivotTable to analyze worksheet data - Microsoft Support

How to use a PivotTable in Excel to calculate, summarize, and analyze your worksheet data to see hidden patterns and trends.

support.microsoft.com

You don't want to disruptively (re-)order your data; you need to - in a separate function elsewhere on the same or an accompanying sheet - replicate it, weight it (3 points for 1st please, 2 for 2nd, etc.) and THEN sort it for display.

 

[BHopkins]

Ah! Yes, I recall working with pivot table before. You are correctly understanding what I'm doing. However, RANK.EQ also did what I need. Success.

Thank you for the help. I'm going to tinker with PivotTable to refresh myself on what it can do.

 

[ekrampitzjr]

Even better, use a randomizer function and have the computer pick the winners. No need to waste time with the actual heats then.

 

[BHopkins]

Yea, I can see it now. I'd have some awfully unhappy parents.

 

[uc50ic4more]

Even better, use a randomizer function and have the computer pick the winners. No need to waste time with the actual heats then.

ChatGPT, please pick the winners of a race featuring contestants named...