Excel question on modelling - Page 2 - Pace and Cap

The Pook · 01-29-2021, 08:14 AM

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

Bill Lyster · 01-29-2021, 09:38 AM

Pook, PM me your email,

I might have something you can use.

Mitch44 · 01-29-2021, 11:08 AM

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

Ted Craven · 01-29-2021, 11:48 AM

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

)

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:

Model.Database.xlsx

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.

Name: filter.examples.jpg
Views: 273
Size: 100.7 KB

Name: filter.examples.jpg
Views: 273
Size: 100.7 KB

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

Ted Craven · 01-29-2021, 12:16 PM

Quote:

Originally Posted by Mitch44

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

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

Ted

Mitch44 · 01-29-2021, 01:22 PM

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

The Pook · 01-30-2021, 11:43 AM

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

DaveEdwards · 02-02-2021, 10:08 AM

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!

01-29-2021, 11:48 AM	#14
Ted Craven Grade 1 Join Date: Jun 2005 Location: Nanaimo, British Columbia, Canada Posts: 8,853	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 ) 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: Model.Database.xlsx 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. 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 __________________ 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

01-29-2021, 08:14 AM	#11
The Pook Grade 1 Join Date: Feb 2014 Location: Loretto, Ontario Canada Posts: 2,538	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

01-29-2021, 09:38 AM	#12
Bill Lyster 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	Pook, PM me your email, I might have something you can use.

01-29-2021, 11:08 AM	#13
Mitch44 Grade 1 Join Date: Nov 2014 Location: The Villages, Fl. Posts: 3,705	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

01-29-2021, 01:22 PM	#16
Mitch44 Grade 1 Join Date: Nov 2014 Location: The Villages, Fl. Posts: 3,705	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

01-30-2021, 11:43 AM	#17
The Pook Grade 1 Join Date: Feb 2014 Location: Loretto, Ontario Canada Posts: 2,538	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

02-02-2021, 10:08 AM	#18
DaveEdwards Grade 1 Join Date: Nov 2008 Location: England Posts: 489	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!