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

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.

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

Good old pen and paper works all the time no problems.
Tim

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 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

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?

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 :eek:. 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

Wow. I’m surprised that a modern spreadsheet like Excel would not automatically read filtered data. Without cutting, pasting, moving etc.

I have used an ancient spreadsheet called Lotus 123 on Mac for years, and you set that up to extract filtered data to a new range where you can work on it. Not RDSS work though because it extracts only to Excel.

So I thought I would try Excel. Thanks for the tips Bill and Ted. I will see what I can do. I would think I could copy filtered data to another master template, or another sheet and read it that way.

Bill I am experimenting with Parx and GP for now.

Pook

Pook, PM me your email,

I might have something you can use.

Can we try to solve other long standing problems such as a proper scratch routine,before attempting all this? Like going from simple to complex so a better version of RDSS is achieved much faster. FWIW my 2 cents.


Mitch

The solution is to use the Excel FILTER function (not just the interactive filtering tools). FILTER is one of a new set of array functions, or 'spill' functions which operate on ranges and produce ranges - an amazing set of new tools. Excel 365 and later or other compatible spreadsheets will have these array functions.

Attached is an example of a reworked MasterTemplate which turns it into what I call a Model Database, which is the way we'll be evolving Excel modeling in RDSS going forward. Here are 3 days from Santa Anita 2021 Jan 8,9,10. All races selected pacelines from those days are in the All Sheet as usual. (Thanks to gfnut for providing a recent set of data :thumb:)

The Filter Examples Sheet does 2 things:

1. the Stats block is moved to the TOP, with data below it. Stats are done for Rows 3 - 9999 (i.e. ALL rows).

2. The data part is drawn by the filter function in cell A13, the first row of data, which references data in the All Sheet. No data is copied. The data returned from the result of the filter function 'spills' into the space around the cell where it is specified.

The filter function specifies:

1. what is the source data (i.e. everything in the All Sheet)

2. filter conditions: in the example, 3 filter conditions are given (though you could have endless conditions) - Result Col I = 1 (Winner), Surface Col E = "D" (Dirt), Distance Col D >= 0 (All distances)

If you change or add any filter conditions, or if you add NEW data to the ALL Sheet, the data automatically updates, as do the Stats.

If you wish to maintain regular queries for different sets of conditions, just create a copy of the Filter sheet and specify different conditions in each.

If you wish to maintain separate Excel Workbooks to draw on the same data, change your filter source to reference the All Sheet in the file which contains the data (rather than referencing the All sheet in the current workbook).


To use this example with your own data, simply copy your own All Sheet data into the All Sheet in this example, replacing the data in the example.


This setup can be made more ergonomic by far, but this gives an idea of how to make this simpler and get started with creative filtering. After that, add the SORT function to order the results in desired ways. This is where RDSS is headed with Excel modeling (including automatic appending of newly exported data to the All Sheet of your Model Database file). Etc ...

Here is the sample database:

Attachment 49555

Again, you need a current version of Excel or a compatible spreadsheet to make the new FILTER function work. Google to find out whether your Excel version supports the new ARRAY functions (including FILTER) and consider updating if this tool seems useful.

If your Filter Examples screen looks something like this, your Excel version supports FILTER.

Attachment 49556

Here is one of my favourite websites for general Excel info, here, the FILTER function and how to use and specify it: https://exceljet.net/excel-functions...ilter-function (although any Google search on 'excel filter function' will return many hits).

Enjoy!

Ted

Let's continue that discussion in a separate thread, here: http://paceandcap.com/forums/showthread.php?p=131647


Ted

Ted is correct in solving this problem with the present version.

1. If your good at writing formulas just change it to reflect the last 20 races.
a. If so it won't delete the other information in the cells for that column unless you actually delete it.

b. Most need to delete much of their data collected anyway as its not current. I.e. thousand race data bases are insane anyway for one particular area. having a thousand race database is OK if broken down into smaller bytes such as by distance, surface and class.

2. If you can't write formulas just copy and paste into another workbook. That way you only need to get the formula correct one time, which should be attainable with help from others with real knowledgeable of this stuff or you fumble around to you get it right.(probably already done and there in the present day RDSS, unsure as I don't use it.

3. Use pencil and paper.

I have done a little work with Excel but I'm more a pencil and paper guy. The future updated version sounds like a vastly improved one over the present. For immediacy just work around it.

Mitch44

Thanks Bill, Ted, and Mitch for the advice and files. I am going to have to screw around with it for a while and see what happens.

Pook

Just to add a little something about Excel. There are lots of things on YouTube.

If you are looking to do something there is a high probability that somebody has posted a video about how to do it.
I have learned a lot from someone who posts as ExcelIsFun.

He has posted thousands of videos. The nice thing about the videos is that you can follow along while you do your work.

Copying his stuff has enabled me to impress others at work!