Sorting Motorsports Statistics

[fetzer]

I keep track of real driver statistics (Nascar) and am involved in an
online racing league and keep track of those stats as well. I've made
a workbook consisting of two main worksheets.

The first worksheet is for Overall Stats and the column headings
consist of driver names, prize money, points, bonus points, wins,
poles, top 5's, etc. The second sheet I've named Data Entry sheet which
consists of a separate chart for each column listed in the first sheet.
In other words I have a Prize Money chart and enter the winnings
manually and then the totals after every race are updated and linked to
the Overall Stats sheet.

The first issue I'm having is the sort feature. After a race I want to
sort by an individual category . First I may want to find out which
driver is leading in overall points and then which won has most wins,
etc. I select the entire Overall Stats sheet (Ctrl + A) and select
Sort from Data menu. (I have do not have any merged cells in any of the
charts). I want to sort in descending order, as my preference, and then
will select the category by which I want to sort. Regardless of which
category I sort by, the issue is that the drivers are not matching up
with the points or winnings that should be assigned to them. It's as if
the rows consisting of each individual drivers stats are swapped with
another driver.

The second issue I'm have is regarding the points column. In Nascar
there are 36 races. After the first 26 races are complete, the top 10
drivers in points are in a race for the championship trophy for the
final 10 races and are awarded additional points. In my spreadsheet I
would like to incorporate a formula that will determine the top 10
drivers after 26 races and then add the appropriate points to those
drivers. Not sure if this is feasible.

I appologize for the long post and am a fairly new user to Excel. I'm
learning my formulas from feedback I receive and others who post in the
Online Community. Any help would be much appreciated.

Thanks very much in advance

 

[davesexcel]

Your first issue sounds like an absolute problem, when you sort the
numbers stay in one spot is that right? do you have the cells as a
relative or absolute value from the data sheet??

So you are sorting each individual catagory one at a time, and then at
the far right total up the rankings to get the total placements of your
league.

I remember having this type of problem in a hocky fantasy draft
many,many years ago, I am sure it has to do with relative or absolute
values, if it is an absolute problem, you need the $A$ signs, goto edit
(say the refernce row is A)
find A
replace with $A$
do this in the sheet that you are doing your sorting do a test run
first to make sure that is the problem

 

[Max]

:
....

The second issue I'm have is regarding the points column. In Nascar
there are 36 races. After the first 26 races are complete, the top 10
drivers in points are in a race for the championship trophy for the
final 10 races and are awarded additional points. In my spreadsheet I
would like to incorporate a formula that will determine the top 10
drivers after 26 races and then add the appropriate points to those
drivers. Not sure if this is feasible.

Here's a sample set-up to play with:
http://www.savefile.com/files/4523840
Nascar Racing ranking set up_fetzer_newusers.xls

Assume 15 drivers listed in B3:B17, with their points per race (these points
are assumed to include bonus points awarded) for the 1st 26 races entered in
C3:AB17

In AC3, copied down: =SUM(C3:AB3)
In AD3, copied down: =IF(AC3="","",AC3-ROW()/10^10)
In AE3, copied down: =IF(AG3=0,"",RANK(AG3,$AG$3:$AG$17))
In AF3, copied down:
=INDEX(B:B,MATCH(LARGE($AD:$AD,ROWS($A$1:A1)),$AD:$AD,0))
In AG3, copied down:
=INDEX(AC:AC,MATCH(LARGE($AD:$AD,ROWS($A$1:A1)),$AD:$AD,0))

Col AC simply totals the points for the 1st 26 races for each driver
Col AD3 acts as an arb tiebreaker col (for cols AF and AG)
Col AE returns the ranking
Col AF returns a full descending sort* of all the drivers
Col AG returns the corresponding total points

*drivers with tied total points, if any, will be listed in the same relative
order that they appear in the original list in B3:B17

Note that if there are ties or multiple ties occurring, then the top "10"
drivers may comprise more than 10 drivers

 

[Max]

Typo, line:

Col AD3 acts as an arb tiebreaker col (for cols AF and AG)

should read as:

 

[paul]

you do select ALL the columns to sort?--
paul
remove nospam for email addy!

 

[fetzer]

To Paul,

To answer your question, yes. I am selecting all columns to sort.
When I hit Ctrl+A the entire sheet, including column headings, are
selected. Then I go to the "Data" menu, click Sort and then choose my
preferred column to sort by.

 

[fetzer]

To: Davesexcel,

Thanks for your reply. To anser the first part of your question, is
yes the numbers stay in one spot, let's say either Total Points or
Winnings, but the drivers names do not change or rotate along with the
sort. The cells are relative value.

The second part of your question would be yes. I would like to sort by
each individual category one at a time. In tracking real-life stats, at
then end of race 10 let's say, I want to know what driver in leading in
points, Greg Biffle, Jeff Gordon, etc. Then I may want to sort by
prize money or top 5's.

 

[fetzer]

Hi Paul,

I appreciate your prompt reply. Yes, all of the columns are selected
when I do the sort. When I hit Ctrl+A the entire chart is highlighted.
Then I will click the Data menu, Sort and then I have the option of
selecting which column to sort by.

Many thanks again,

Doug (fetzer)

 

[fetzer]

To Max,

All I can say is WOW! Your sample setup-file was great and I can't
thank you enough!

One question I have if you don't mind. At the end of the 26th race,
the top 10 drivers are reassigned, with the driver in the first
position receiving 5050 points, 2nd postion receives 5045, etc. Let's
say at the end of the 26th race, the driver in 1st has 3425 total
points. That driver then needs an additional 1625 points added to his
existing total to make it to 5050 points. Can I manually add those
additional points (1625) to column AC or AD or would an additional
column (AH) be better to add those points?

Thanks again for your help. You have some fantastic ideas.

Doug (fetzer)

 

[Max]

To Max,

All I can say is WOW! Your sample setup-file was great and I can't
thank you enough!

One question I have if you don't mind. At the end of the 26th race,
the top 10 drivers are reassigned, with the driver in the first
position receiving 5050 points, 2nd postion receives 5045, etc. Let's
say at the end of the 26th race, the driver in 1st has 3425 total
points. That driver then needs an additional 1625 points added to his
existing total to make it to 5050 points. Can I manually add those
additional points (1625) to column AC or AD or would an additional
column (AH) be better to add those points?

Thanks again for your help. You have some fantastic ideas.

Doug (fetzer)

You're welcome, Doug !

Some thoughts to extend the earlier set-up ..

See sheet: Y in the revised sample at:
http://www.savefile.com/files/9470681
Nascar Racing ranking set up_v2.xls

Assume the Top 10 "Final" Pts (fixed pot) is listed in AH3:AH12,
eg: 5050, 5045, 5040, ... 5005

Put
In AI3: =SUMIF(AE:AE,AE3,AH:AH)/COUNTIF(AE:AE,AE3)
In AJ3: =AI3-AG3
Select AI3:AJ3, copy down to AJ12

Col AI adjusts the fixed pot points in col AH for ties, if any, within the
top 10
Col AJ simply computes the points difference top-ups made to the totals in
col AG

 

[fetzer]

To MAX:

My apologies for the delay to your last response to a question I had.
My work schedule has not been so kind to me the last few days.

This is just a note to say I appreciate the work you have done for me
regarding my Motorsports Statistics spreadsheet. Last week I submitted
a couple of questions and you responded ever so kindly and promptly.
Your ideas have opened up a number of new "doors" for me in the Excel
world.

Sincerely and best wishes,

Doug (fetzer)

 

[Max]

You're welcome, Doug !
Appreciate the feedback, thanks