Exel or Google Function for Sort
 

I am hoping someone can help me with how to write the function as I can not see the forest through the trees. I have created my Handicapping workbooks in Google Sheets as opposed to Exel. The question I have or perhaps somebody here can give me how to write my function. Ok, here goes. After I have calculated all my numbers in numerical order of the post positions and horses names/jockeys M/L. I have my 5 five angles in vertical order according to simple formulas..i.e. So Post position # 1(including saddle color) Horse name in next column, then I have my final score or overall # Speed, Form, Pace, Class, (Intangible). I am hoping somebody can tell me what formula I can use after my final numbers are in so that I can sort into column with either each column in their final order, and the final order sorted with horse name Number in order i.e. switch from original order back to final order etc. Any concrete help would be great...I know their are coders/softwares out there...I have one ..I just enjoy doing this myself and was hoping someone knows how to write this particular function or unique function etc that can do this. Look forward to replies.

Chuck

See if google sheets has a macro recorder. There's so much on youtube and forums for programming instruction, the best thing is to just keep at it. Each week you can work through a new problem, you'll get there eventually. I would recommend excel, to my grave i'll say that.

Hi Chuck,

Using Excel, presuming your 8 data columns -

Program #
Horse Name
Final Score
Speed
Form
Pace
Class
Intangible

- are in Columns A - H respectively, in a Sheet named Data -

- make a new Sheet named (e.g.) 'Sort'. In Cell A1 enter the following formula:

=SORT(Data!A:H,n) -- where 'n' is the column number you want to sort by. Your data found in the sheet Data will appear in the Sort sheet sorted by the column you are interested in.

For example, to sort by Final Score, use =SORT(Data!A:H,3) (if Final Score is the 3rd column)

You can spice it up a bit by putting the column # to sort by in a cell (say, I1) then use the formula =SORT(Data!A:H,$I$1). That way you can sort by your different factors by changing the sort column number in Cell I1.

If you factors are calculated such that highest value = best, then add the sort direction to your formula: =SORT(Data!A:H,$I$1, -1) for Descending order (highest to lowest). For the reverse order, omit the 3rd argument, or else use 1 (=SORT(Data!A:H,$I$1, 1.

Throw some random colours in there and now you're an Excel genius! (Not sure about a Google Sheets genius ...)

Good luck!

Ted

Question: I know people charge for their pars
 

Ted..so kind of you... Another question for all the great help out there..Anyone,,,.. giving away free pars for the 2022 campaign ???Just hoping:cool:

Another question from Stupid
 

Ok..Ted..My page I am sending you has my easily equated formulas and become filled in(obviously after I have inputed my numbers) I have opened a new sheet- so I am really stupid..or slow I think but I try hard to learn and once I learn get it. This is an ever ending project to which I am sure you know. So I have attached a snip it. of the final numbers without adding my colors for 1st 2nd and 3rd etc. (If you have the time)

Your image shows 10 columns of data - Columns A - J.

Presuming your main sheet is named Data, in your second sheet, in Cell A1, enter the formula:

=SORT("Data"!A:J, 5, -1) to sort the data in Columns A - J by the 5th column ('Final') in descending order (highest to lowest values).

If you want to sort by the 'Speed' Column, change the 5 above to 6. Etc ...
If your main sheet is named something other than "Data" (e.g. "Sheet1") then change the part of the above formula from "Data" to "your sheet name". (Use the "double-quote" characters if your sheet-name has any spaces in it, otherwise you don't actually need the "double-quote" characters.)

When you enter the above formula, the original data will appear on your 2nd sheet sorted by the column order which you chose, in descending numerical order (highest to lowest).

Good luck, good skill!

Ted

Thanks Mr. Craven
 

Thanks Mr. Craven. I am going to try this, however, if it does not work out, would have some time to take a look at what I have designed. As, most software and spreadsheets. Google Sheets allows sharing and I can allow access for you to take a look and tweak if you like. I am trying desperately trying to still do the work as I believe that handicapping and inserting numbers as I go along in the process is whole hearted passion of the game. I am just not a complete numbers guy, still go by a gut feeling after pouring over numbers as oppose to having the whole thing spit it out and there it is.

I am working on fine tuning as I go, Dirt to Sprint ideas, speed regression in terms of age of the horse and cycles. I also looking at trying to put forth at some point a comprehensive percentage # in terms of breeding statistics that will be part of my intangibles in first time starters/or young maidens who are in the Md.Sp.ranks and low start status. Etc. I truly appreciate help from those who take the time to answer. It is a pleasure to know that there are great people who invest their time in the process of computer handicapping.

Cheers

Chuck

If you discover that Google Sheets cannot perform the functions you need, you should investigate the Open Office software free spreadsheets.

Chuck, no worries - send me by email a copy of your Google Sheet, or a link to it if it's online. I don't use Google Sheets (or Open Office) so I can't spend too much time learning it or installing it locally, but it's pretty compatible with Excel so I should understand what's going on.

I think you have my email from Paceandcap.com, but if not, just PM me here and I'll give it to you.

Cheers,

Ted

google sheets...is part of the application programs if you have a gmail account, it is part of the google docs systems. I believe it was google's ways of telling Mr. Gates to go screw himself and Windows for charging people to use his work applications as part of his operating system plus the obvious pirating that goes on. I have exel and it is amazing, the unfortunate part is I am out of work right now, and like most gamblers, money is very tight to say the least and I have cut my subsription off until further notice. Sheets is I believe is about the same program, they do a few things different in terms of it's interactions etc. Anyways.. again, I will e-mail you the link and when you have time, or take your time, there is no rush...I have working on these spreadsheets in trying to make it look good, to making it smaller. More or less it was just about getting comfortable reading and understanding spreadsheets from basics but I have gotten better at understanding them but I had a hard time getting through calculus in grade 13 and dearly wished that I did not drop algebra and Functions. LOL. I look forward to your response and thanks so much again.

Chuck

Okay, I have created new sheet and typed in formula, however, what am I doing wrong as when I do the function I keep getting an error. Error states pars error.

This is working copy of my book and my book I am constantly tickering with the sheets..so this what I call "Final Book Sheet". I then opened up a new empty sheet and tried to type in sort column...attach copy... On the empty sheet, walk me through like a little kid...as I want to take my final numbers including Program # s Horse Name Column/Row all the way to the Int. and be able to have the Final #s from highest-descending for each column? HELP.

Chuck