horse racing pivot table jockey stats

Jack_

New Member
Joined
Jun 22, 2011
Messages
4
Hi everyone,

I am trying to use a pivot table to initially analyze jockey stats and I am having difficulties in getting totals for different items.

What I am trying to produce is:

Name..Rides..Win................Place...............Show
Pardo 4 ........ 1 25% $4.00 2 50% $5.50 3 75% $ 6.60

The data column headers are:
Horse_Name, Finish, jockey, winpay, placepay, showpay

The data is:
Lone Boy 1 Pardo 4.00 3.00 2.00
Mary One 2 Pardo 0.00 2.50 2.10
Good Two 3 Pardo 0.00 0.00 0.00
Blackacre 4 Pardo 0.00 0.00 2.50

Obviously there are other horses, jockeys, races, etc.

I want to use a pivot table because I can filter the data by year, odds rank, odds, and other header labels. The original data set has about 100,000 lines of data and 80 columns.

I can get the total number of rides by counting on the jockey's name. The problem is breaking out the rides to reflect only a 1st, 2nd, and 3rd place finish and having the percentage relate back to the total number of rides. I have tried various filtering mechanisms and calculated fields without sucess.The problem is usually that the order of finishes gets lumped into the same number, so 1st, 2nd, 3rd are all the same numbers.

I am using Win 7 and Excel 2010.

Any suggestions would be greatly appreciated.

I have not posted before and I think I have included the information needed to solve the problem. If I have not, just let me know and I will include it.

Thanks in advance,

Jack
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Normalize your table, so that it looks like this:

Excel Workbook
ABCDE
7Horse_NameFinishjockeyPay categoryAmount
8Lone Boy1Pardowinpay 4.00
9Lone Boy1Pardoplacepay 3.00
10Lone Boy1Pardoshowpay 2.00
11Mary One2Pardoplacepay 2.50
12Mary One2Pardoshowpay 2.10
13Blackacre4Pardoshowpay 2.50
Sheet1



... by using this method:
http://www.tek-tips.com/faqs.cfm?fid=5287
( to get to the PivotTable Wizard in Excel 2007 it's Alt-D-P ... I hope it's the same in Excel 2010 )
Tip: concatenate the horse/finish/jockey into a single field before doing the normalization, so that you have a single row identifier. Split it afterwards whent the process is complete ( e.g. separate your field with "/", and use Data/Text To Columns to split your field afterwards ).

This is my pivottable of the normalized data:
Excel Workbook
FGHIJKLMNO
17Pay categoryData
18winpayplacepayshowpay
19jockeyCount of AmountSum of AmountSum of Amount2Count of AmountSum of AmountSum of Amount2Count of AmountSum of AmountSum of Amount2
20Pardo124.84%4.00234.16%5.50340.99%6.60
Sheet1



For this I added the Amount to the data area 3 times ... for one of them I changed the Field setting so as to summarize by Count, and another one I chose to Show Values as % of row.
 
Upvote 0
Thanks Glen,

A very good approach to solve the problem that I put forth and one which shows an impressive level of technical competence. I am going to have to study it further. I have already begun to do some conversion on the example data I gave, but I have to study the references you included in more detail.

I must admit that normalizing the data never occurred to me within this context. The data arrived from Equibase Results Charts as XML flat files with individual records for each horse in the race at each call. It is some bulky and very redundant. I had initially thought of putting the data into MS Access to cut down on the size of the database and make it more efficient, but then decided against it because while Excel comes as a part of most MS Office packages, Access is found either as a stand alone or part of much higher end Office bundles.

When I have a better grasp of the situation I will write back – probably with some more questions. In the meantime I can see one problem with both approaches and, from the bolded words at the end of your post, I suspect you have seen it also.

What I don’t know how to do within the context of a pivot table is to have the individual counts expressed as a percentage of another field total as opposed to the end row total. Specifically, I need to have columns with win_finishes/starts, place_finishes/starts, etc. The way the results come out now are win_finishes/(win_finishes+place_finishes+show_finishes). I think that is what you pointed out with you bolded statement, % of row.

I can produce a column with total starts for each jockey using the unnormalized data and have tried everything including calculated fields, but I can’t find a way of doing the percentages of other than the end row. Any suggestions?

Thanks again and take care,

Jack
 
Upvote 0
HI Glen,

Actually have another question I forgot about.

Is there a way to do a count on a field that depends upon the value of the field?

For example, a column contains the following values
0
0
2
4
0
5
-6
-2

Can I do a count only when the values are greater than zero?

Using the above data the count I would want is 3

Thanks

Jack
 
Upvote 0
I can produce a column with total starts for each jockey using the unnormalized data and have tried everything including calculated fields, but I can’t find a way of doing the percentages of other than the end row. Any suggestions?
Ah yes, I see what you mean about the percentages now. If you have a dummy column of zeros, headed Starts, in your unnormalized data, before you convert it to be normalized, then once you have the normalized version you can use that field in your Pivot settings. But you will end up with a dummy entry in the pivottable, like this:

Excel Workbook
GHIJKLMNOPQRS
23*CategoryData**********
24*winpay**placepay**showpay**starts**
25jockeyCountsPcentsSum of AmountCountsPcentsSum of AmountCountsPcentsSum of AmountCountsPcentsSum of Amount
26Pardo125.00%4.00250.00%5.50375.00%6.604100.00%0.00
Sheet6


The Pcent field is actually a Count Of Amount, with a setting of Show as % of Base Field:= Category, Item:=Starts

As for your other query about counts of items greater than zero ... I recommend another new column in the normalized data.

I hope that helps.
 
Upvote 0
Hi Glen,

I have been working hard and I think I have most of the problems solved.

I do have a few remaining problems to solve but I won't be able to get to those until Sunday.

Thanks again.

Jack
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top