Excel Question

[traveler]

I have a spreadsheet with the first column being the date and the second column the race number and the third is horse with other data columns for each horse following. I may have anywhere from 3 to 5 contenders per race and would like to be able to have a count of how many races I have in the spreadsheet on an on-going basis. So my first 3 races might have 3 horses in race 1, 4 in race 2 and 5 in race three. With a few races I could eyeball it and see I have three races but with hundreds I need a formula to calcultae the number of races. I am stumped. Any help will be appreciated.

[bigmack]

http://office.microsoft.com/en-us/excel/HA011266271033.aspx?pid=CH010005131033

[traveler]

Thanks for the link, will check it out this weekend. Should have done a search myself.

[kingfin66]

I have a spreadsheet with the first column being the date and the second column the race number and the third is horse with other data columns for each horse following. I may have anywhere from 3 to 5 contenders per race and would like to be able to have a count of how many races I have in the spreadsheet on an on-going basis. So my first 3 races might have 3 horses in race 1, 4 in race 2 and 5 in race three. With a few races I could eyeball it and see I have three races but with hundreds I need a formula to calcultae the number of races. I am stumped. Any help will be appreciated.

Here's an idea Traynor. Try using the COUNTIF function. Use column C for the function. Say you have 100 rows of data starting with row 2. Your COUNTIF function would look like: COUNTIF(C2:C100,">0")

The result, assuming you have a contender in every race, should be 99.

[spilparc]

I have a spreadsheet with the first column being the date and the second column the race number and the third is horse with other data columns for each horse following. I may have anywhere from 3 to 5 contenders per race and would like to be able to have a count of how many races I have in the spreadsheet on an on-going basis. So my first 3 races might have 3 horses in race 1, 4 in race 2 and 5 in race three. With a few races I could eyeball it and see I have three races but with hundreds I need a formula to calcultae the number of races. I am stumped. Any help will be appreciated.

Post a link to your spreadsheet, and I'll take a look. A lot depends on how you have it set up, are there any spaces, etc.

[kingfin66]

Oops. Traveler, I inadertently called you Traynor in my reply, I apologize for the error. It's that whole "Tr" think. Hopefully, my info helps.

[Ponyplayr]

Oops. Traveler, I inadertently called you Traynor in my reply, I apologize for the error. It's that whole "Tr" think. Hopefully, my info helps.
Hey I am sure you made Traynors day.....If you want to impress him call him Indulto :lol: :lol: That would qualify as a rise in class :lol:

[kingfin66]

I think I'm going to stay away from all of that! :)

[traveler]

I think I went up and class and Traynor dropped to the carnival pony circuit.

[KYJACK]

I have a spreadsheet with the first column being the date and the second column the race number and the third is horse with other data columns for each horse following. I may have anywhere from 3 to 5 contenders per race and would like to be able to have a count of how many races I have in the spreadsheet on an on-going basis. So my first 3 races might have 3 horses in race 1, 4 in race 2 and 5 in race three. With a few races I could eyeball it and see I have three races but with hundreds I need a formula to calcultae the number of races. I am stumped. Any help will be appreciated.Traveler
If you're still looking for a solution, here's an example of how to count the number of races in your spreadsheet. I had to solve the same problem this past summer. Of course this probably isn't the only way or even the best way, to do it! But, this should get you started!

(BTW. I assuming you actually have a variable # of horse rows per race and need to count just the actual races. i.e. I thought you meant to say:

"and the third column is horse NAME/NUMBER with other data ROWS for each horse following."

If not, then the following isn't quite what you need, your solution will be much simpler and I apologize for my misinterpretation! :bang: )

What I did was to add two extra columns to the spreadsheet for calculations and 3 sets of formulas

1) One, the "Race Chg" column calculates and marks the start of a new race. A new race is considered starting when there's at least one change in the 'Race Date', the 'Track Name', or the 'Race Number'. When the new race is detected, a '1' is placed in the column's cell, otherwise it's blank. i.e. "0".

2) The other is a 'Start of Race" indicator (SOR), that provides a quick visual indication of the start of the each race, when you manually look over the data.

3) Finally to get the total races, we merely add up the number of detected race starts. This last formula is placed near the top of the spreadsheet for easy reference.

The formulas are as follows:

1) Race Change Detector Formula (Colum F, starting at Cell F9)
=IF(A9="","",IF((C9<>C8)+(B9<>B8)+(A9<>A8),1,""))

2) SOR Marker Formula (Column G, starting at Cell G9)
=IF(F9=1,"==========","")

3) Actual Race Count Formula (Cell C6)
=SUM(F9:F24836)The two column formulas (1 & 2) are initially placed, as listed above, in row 9. Then they are block copied to all of the remaining lines. EXCEL will of course, automatically change the cell addresses in the formulas. In the attached zip file, the sample spreadsheet is set up for 24836 lines of horses! However, to save space and bandwidth, only 4 races are populated with sample values in the attached spreadsheet. You use the "Set Print Area" command for only printing out the actual race data. Horses are added from Row 9 downward. Any following blank lines of data will not have any calculations data displayed. A fully populated spreadsheet can create a big file due to the extra formulas! If you fill it up, you best be thinking about a database such as ACCESS. Actually with EXCEL, I think you can take it up to 65K lines of data!

Finally the third formula is used one time to count the 'Race Chg' counts to derive the "Total Number of Races".

One way to improve and enhance the spreadsheet, is to go 3-D. That is, use more than one spreadsheet in the workbook. For example one sheet would only contain your raw input data. Another could contain calculation lines such as those described above. Another sheet could be used for any output summary/formatted data, such as the above race count value. Or, say you wanted to reformat the data and then export the output sheet for importing into another program.

Enjoy!
Jack

Opps! The Zip file is too big! I'll try to upload a smaller version in my next post!

Jlw

[KYJACK]

Opps! The Zip file is too big! I'll try to upload a smaller version in my next post!JlwOK! Let's try it again!
Here attached is a small spread sheet illustrating the concept.

For easier editing, you really want to upgrade this and enhance via the 3-D route. Your first page is the raw data - you can easily add and remove lines of horses. Calculation should be on another page, so you don't inadvertently mess with the formulas, which always stays the same!

Jack

[spilparc]

Here's an example of a simple spreadsheet I set up that does what I THINK you want. Take a look and let me know.

In cell D2 insert this formula

=COUNT(B3:B1001)

On 12/06 race 1 you have three contenders.
On 12/06 race 2 you have three contenders.

On 12/11 race 2 you have two contenders.

There are a total of 10 races even though there are about 17 rows. It should even allow you to insert spaces between the dates.

Steve

[traveler]

Jack your quote "I assuming you actually have a variable # of horse rows per race and need to count just the actual races." is exactly what I want to do.
Not only don't I understand Excel too good but I don't "splain" things too good either.I will look at your solutions - Thanks.

Spil- Thanks too! Plan to look at these solutions tonight - or today if the boss stays out of my office :cool:

[traveler]

Jack - Your formula works great, just a couple changes for what I am doing. I have Open Office Orgs. free office suite which includes a dbase and am going to be looking at maybe going to that - I can't justify cost of Access. Thank again

Thanks for your efforts too Steve. :)

[KYJACK]

Jack - Your formula works great, just a couple changes for what I am doing. I have Open Office Orgs. free office suite which includes a dbase and am going to be looking at maybe going to that - I can't justify cost of Access. Thank again

Thanks for your efforts too Steve. :)Traveler

:cool: Kool! Glad it's working for you! :jump:

Open Office does look interesting!

Regards!

Jack