Go Back   Pace and Cap - Sartin Methodology & The Match Up > General Discussion
Mark Forums Read
Google Site Search Get RDSS Sartin Library RDSS FAQs Conduct Register Site FAQ Members List Search Today's Posts

General Discussion General Horse Racing Discussion

Reply
 
Thread Tools Display Modes
Old 01-27-2021, 12:18 PM   #1
The Pook
Grade 1
 
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,535
Excel question on modelling

I am not that familiar with Excel. Fooling around with modelling. Can anyone tell me how to keep the count current. When I insert rows to add data the count at the bottom of the master template stays at 18. I have 250 rows or so. When I filter I want it to count the filtered rows without physically entering row info in each relevant counting cell. Must be some simple thing I am overlooking. See below.

Thanks
Pook
Attached Images
 
The Pook is offline   Reply With Quote
Old 01-27-2021, 01:20 PM   #2
57viking
Grade 1
 
Join Date: Jun 2020
Location: Vermont
Posts: 134
Excel sheet

The count command is not straightforward with filtered data. I changed the template to a google sheet and do all my model work using the query command set to get around some of these issues. I can share it if you would like.
57viking is offline   Reply With Quote
Old 01-27-2021, 01:45 PM   #3
The Pook
Grade 1
 
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,535
If that is the case, the % ranked info is meaningless. It uses the information from the first 18 rows only? Seems implausible to me.

Pook
The Pook is offline   Reply With Quote
Old 01-27-2021, 03:56 PM   #4
Lt1
Grade 1
 
Lt1's Avatar
 
Join Date: Apr 2012
Location: Valley Stream NY
Posts: 9,042
Good old pen and paper works all the time no problems.
Tim
__________________
Trust but verify
Lt1 is offline   Reply With Quote
Old 01-27-2021, 08:35 PM   #5
Bill Lyster
Grade 1
 
Bill Lyster's Avatar
 
Join Date: Feb 2006
Location: Escondido CA just 25 minutes from where the turf meets the surf - "...at Del Mar"
Posts: 2,418
Quote:
Originally Posted by The Pook View Post
I am not that familiar with Excel. Fooling around with modelling. Can anyone tell me how to keep the count current. When I insert rows to add data the count at the bottom of the master template stays at 18. I have 250 rows or so. When I filter I want it to count the filtered rows without physically entering row info in each relevant counting cell. Must be some simple thing I am overlooking. See below.

Thanks
Pook
Look at the range for the COUNT. The easy way is to add new lines, inside the existing range. If you enter a new line outside the range you will need to edit the range so that the count and the other things get considered. so in this example add a line between lines 159 and 187 and the count will update.

In the three lines that follow you need to follow the format shown for % ranked #1. on the next line use the same range but instead of "=1" change it to "=2",etc. You can copy this info straight from the template subdirectory and just change the range to fit your data.

This screen shot is from the current PRX 6 furlong maiden summary and it shows that the top three BL and Vdc win 84% of the time when in the top 3 and 89% in the top 4.

Name:  ScreenHunter_1254 Jan. 27 17.25.jpg
Views: 496
Size:  58.5 KB
Bill Lyster is offline   Reply With Quote
Old 01-27-2021, 10:15 PM   #6
The Pook
Grade 1
 
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,535
Quote:
Originally Posted by Bill Lyster View Post
Look at the range for the COUNT. The easy way is to add new lines, inside the existing range. If you enter a new line outside the range you will need to edit the range so that the count and the other things get considered. so in this example add a line between lines 159 and 187 and the count will update.

In the three lines that follow you need to follow the format shown for % ranked #1. on the next line use the same range but instead of "=1" change it to "=2",etc. You can copy this info straight from the template subdirectory and just change the range to fit your data.

This screen shot is from the current PRX 6 furlong maiden summary and it shows that the top three BL and Vdc win 84% of the time when in the top 3 and 89% in the top 4.

Attachment 49551
Thanks Bill, I will look into that.

Pook
The Pook is offline   Reply With Quote
Old 01-28-2021, 10:58 AM   #7
Ted Craven
Grade 1
 
Ted Craven's Avatar
 
Join Date: Jun 2005
Location: Nanaimo, British Columbia, Canada
Posts: 8,853
The best thing to do is to INSERT new lines of data at row 3 (the initial row). This accomplishes 2 things:

1. data is collected naturally in reverse chronological order (most recent races at the top)

2. more important - when you INSERT new rows within existing ranges which are used in cell formulas (i.e. all formulas in the Stats block), Excel automatically updates the formulas to a new range size and thus the formulas reflect data within the new range.

Conversely, when you insert rows which do not affect a formula (i.e. if you inserted/appended new rows after the initial blank rows above the stats block, or below the stats block) Excel has no changes to make to any existing formulas, thus the Stats block continues to reflect only the data within the existing range.

As Bill L shows, check the formula for any Count or other cell in the stats block, noting the range used (initially Row 3 - row 20 e.g. Q3:Q20). INSERT a single row at row 2 (right-click on the row # at the left edge and choose INSERT from the menu). Now notice that ALL formulas are updated to reflect an extra row: e.g. Q3:Q21).

There are MUCH better ways to collect stats (in addition to paper and pencil ) and with some others who have offered help, I hope to present this year a new Master Template layout which resembles the Analysis tabs layout and which you can filter by finish position (e.g. Win, Place), distance range, surface, race type, etc. Also hopefully inserted automatically by RDSS into a specified Model sheet.

Meanwhile, the one we've got ...

Hope this helps!

Ted
__________________

R
DSS -
Racing Decision Support System™
Ted Craven is offline   Reply With Quote
Old 01-28-2021, 02:41 PM   #8
The Pook
Grade 1
 
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,535
Quote:
Originally Posted by Ted Craven View Post
The best thing to do is to INSERT new lines of data at row 3 (the initial row). This accomplishes 2 things:

1. data is collected naturally in reverse chronological order (most recent races at the top)

2. more important - when you INSERT new rows within existing ranges which are used in cell formulas (i.e. all formulas in the Stats block), Excel automatically updates the formulas to a new range size and thus the formulas reflect data within the new range.

Conversely, when you insert rows which do not affect a formula (i.e. if you inserted/appended new rows after the initial blank rows above the stats block, or below the stats block) Excel has no changes to make to any existing formulas, thus the Stats block continues to reflect only the data within the existing range.

As Bill L shows, check the formula for any Count or other cell in the stats block, noting the range used (initially Row 3 - row 20 e.g. Q3:Q20). INSERT a single row at row 2 (right-click on the row # at the left edge and choose INSERT from the menu). Now notice that ALL formulas are updated to reflect an extra row: e.g. Q3:Q21).

There are MUCH better ways to collect stats (in addition to paper and pencil ) and with some others who have offered help, I hope to present this year a new Master Template layout which resembles the Analysis tabs layout and which you can filter by finish position (e.g. Win, Place), distance range, surface, race type, etc. Also hopefully inserted automatically by RDSS into a specified Model sheet.

Meanwhile, the one we've got ...

Hope this helps!

Ted
Okay I got it to work. But now (and I can't find an clear answer on line) when I filter the data, the count info reflects the entire database. Eg. when I filter all the races to just show 6F, the count and count percentages don't update to just those races.

Thanks
Pook
The Pook is offline   Reply With Quote
Old 01-28-2021, 04:31 PM   #9
Bill Lyster
Grade 1
 
Bill Lyster's Avatar
 
Join Date: Feb 2006
Location: Escondido CA just 25 minutes from where the turf meets the surf - "...at Del Mar"
Posts: 2,418
After you filter your races by distance, you need to create about 12 blank rows beneath your last entry FOR EACH DISTANCE. Then copy the COUNT and % info about 3-4 spaces below the last entry for each distance and then update the range for each of the 9 rows. You do this for just one column. After you check that your ranges represent what you want them to be, copy the entire column and paste it as far across the worksheet as you want. You will get summed info for many factors that you will not ultimately use, but just hide those columns.

Another thing, once you start to see usable patterns develop, DO NOT DELETE columns of data that you don't like. Keep them in, because as you add races to your data base they will go into those columns that are now hidden. Otherwise you will be getting data from the wrong column in your database which will nullify all your hard work to date.

What track(s) are you profiling?
Bill Lyster is offline   Reply With Quote
Old 01-28-2021, 04:40 PM   #10
Ted Craven
Grade 1
 
Ted Craven's Avatar
 
Join Date: Jun 2005
Location: Nanaimo, British Columbia, Canada
Posts: 8,853
Filtering does not remove the data, merely hides it from view, thus formulas based on the range which you filtered still count hidden values.

Try Bill's suggestion, or check out these references using the Excel SUBTOTAL function:

http://www.microknowledge.com/subtot...ed-data-excel/
https://www.automateexcel.com/formulas/subtotal-if/

HOWEVER - this is all rather a pain . There are much better ways to keep exported model data, and I am working on a set of specs which I will publish within a few weeks for commentary, which will be much more flexible and comprehensive, much better filtering/sorting, looks just like the RDSS Analysis set of Tabs, with no copying and pasting/inserting.

Ted
__________________

R
DSS -
Racing Decision Support System™
Ted Craven is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adjustments Question Ted Craven RDSS2 / FAQ's 3 06-29-2014 03:22 PM
RDSS2 - Adjusted Screen Question Ted Craven RDSS2 / FAQ's 4 02-16-2013 06:45 PM
A Question Posed 1986 Bob Cochran General Discussion 12 08-30-2009 07:29 PM
Release Notes - Version 0.98.8 Ted Craven RDSS Info, Reference 3 07-28-2009 08:35 PM
Question Concerning "About" Distances... lueylump RDSS 2 05-13-2009 04:45 PM


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.

All times are GMT -4. The time now is 09:09 AM.