|
Google Site Search | Get RDSS | Sartin Library | RDSS FAQs | Conduct | Register | Site FAQ | Members List | Today's Posts | Search |
General Discussion General Horse Racing Discussion |
|
Thread Tools | Display Modes |
01-27-2021, 12:18 PM | #1 |
Grade 1
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,538
|
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 |
01-27-2021, 01:20 PM | #2 |
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.
|
01-27-2021, 01:45 PM | #3 |
Grade 1
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,538
|
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 |
01-27-2021, 03:56 PM | #4 |
Grade 1
Join Date: Apr 2012
Location: Valley Stream NY
Posts: 9,145
|
Good old pen and paper works all the time no problems.
Tim
__________________
Trust but verify |
01-27-2021, 08:35 PM | #5 | |
Grade 1
Join Date: Feb 2006
Location: Escondido CA just 25 minutes from where the turf meets the surf - "...at Del Mar"
Posts: 2,418
|
Quote:
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. |
|
01-27-2021, 10:15 PM | #6 | |
Grade 1
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,538
|
Quote:
Pook |
|
01-28-2021, 10:58 AM | #7 |
Grade 1
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
__________________
RDSS - Racing Decision Support System™ |
01-28-2021, 02:41 PM | #8 | |
Grade 1
Join Date: Feb 2014
Location: Loretto, Ontario Canada
Posts: 2,538
|
Quote:
Thanks Pook |
|
01-28-2021, 04:31 PM | #9 |
Grade 1
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? |
01-28-2021, 04:40 PM | #10 |
Grade 1
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
__________________
RDSS - Racing Decision Support System™ |
|
|
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 |