Wanted: Microsoft Excel Formula - Quirin Speed Points

[Dan H]

Thanks again Dave, the 2003 file opened successfully. It will take me a little while to digest it.

Dan H

[raybo]

Originally Posted by Dave Schwartz

Okay... I have been playing with this.

I have the sprints thus far.

Please note that this is just the calculation AFTER you have what you need from the pacelines loaded into a 10-race grid.

http://www.horsestreet.com/BBSImages/QuirinEsPts.xlsx

LOL,

You tackled the problem like I was proceeding. Rather than trying to put all the options in one very long formula, possible in Excel 2007, I, too, opted to create several columns of "tests". I got as far as looking at the bonus point for in the lead or within a neck in all 3 rated races. You beat me to the punch.

May I ask you a question about your formula syntax? Instead of using traditional "IF" statements you seem to accomplish the same thing by enclosing the criteria in parenthesis and then multiplying by 1. Can you explain how this works and is this syntax something outside the realm of Excel help files?

Thanks!!

[Dave Schwartz]

Sure.

Pretty simple really, but executes much faster than an if-then statement because it is all math.

I have a cell called "Sprint?" that is populated with a statement like this:

(B6<8)*1

Since cell B6 contains the distance of the race, that is really the same as saying:
(Dist<8)*1

(B6<8) is a "truth" statement, just like an if-then. If you put just that into a cell, the cell will read "true" or "false." Actually the "*1" is probably unneccessary - it just reads better for me.

So, if the statement is true, it produces a "1" otherwise a "0."

Then, later on, instead of saying "If the race is a sprint" or, more accurately, (B6<8) I simply mulitply a value times that cell.


Hope this helps. I doubt that I will spend any more time on this.

I think the coolest part of the table is the part where you decide - based upon the ratable races - how many points the horse gets, and whether or not he qualifies for a penalty or a bonus.

BTW, be aware that I did not bother with only going back 5 races maximimum. Personally, I will go back as many as necessary to get the 3 ratable races.


Dave

Dave

[raybo]

Originally Posted by Dave Schwartz

Sure.

Pretty simple really, but executes much faster than an if-then statement because it is all math.

I have a cell called "Sprint?" that is populated with a statement like this:

(B6<8)*1

Since cell B6 contains the distance of the race, that is really the same as saying:
(Dist<8)*1

(B6<8) is a "truth" statement, just like an if-then. If you put just that into a cell, the cell will read "true" or "false." Actually the "*1" is probably unneccessary - it just reads better for me.

So, if the statement is true, it produces a "1" otherwise a "0."

Then, later on, instead of saying "If the race is a sprint" or, more accurately, (B6<8) I simply mulitply a value times that cell.


Hope this helps. I doubt that I will spend any more time on this.

I think the coolest part of the table is the part where you decide - based upon the ratable races - how many points the horse gets, and whether or not he qualifies for a penalty or a bonus.

BTW, be aware that I did not bother with only going back 5 races maximimum. Personally, I will go back as many as necessary to get the 3 ratable races.


Dave

Dave

Thanks Dave,

Your explanation is very understandable. I thought that since the "IF" statement produces a discrete output, then the output would be either a 1 or a 0. The "*1" was the tipoff. However, like you, I wondered if the "*1" was necessary in that particular test.

I guess what is confusing is how Excel knows that the "()" designates an "IF" statement. I assume it's part of the Visual Basic programming language. Is that correct?

I'm very glad to know this, it will make creating long nested "IF" statements much easier.

I think I lean towards your thinking concerning going back past the last 5 races for the 3 ratable races. In my experience, sometimes one must go back further than the last 2, 3, 4, or 5 races to get at a horse's true potential, considering current form.

I also found the part where you find the 3 ratable races, prior to adding a bonus point or deducting a point, as the most challenging, at least where my nested "IF" statements are concerned.

[Dan H]

Thanks to Raybo and Dave. You've pushed me in the right direction. Until I understand the concept behind Dave's solution, I'm going to build nested IF statements (a'la Raybo's solution). Build from there. Again, thanks.

Dan H

[raybo]

Originally Posted by Dan H

Thanks to Raybo and Dave. You've pushed me in the right direction. Until I understand the concept behind Dave's solution, I'm going to build nested IF statements (a'la Raybo's solution). Build from there. Again, thanks.

Dan H

Yeah, I hope he posts again and explains the theory behind his syntax a little more.

[Tom]

That is a handy little nugget dropped on us.

[raybo]

Originally Posted by Tom

That is a handy little nugget dropped on us.

For sure! But I would like to know how the syntax works and why.

[Dave Schwartz]

Raybo,

I do not understand how I can help you better than I did.

You have the code. It does what it does.

If I said, "(X=14)" that is a truth statement that results in a 0 or 1. If X is anything except exactly 14 it is false, i.e. a 0.

It works just like "If X=14 then POST TRUE else POST FALSE."


Dave

[Dave Schwartz]

Here is a link to a great website about Excel:

http://www.mrexcel.com/

[raybo]

Originally Posted by Dave Schwartz

Here is a link to a great website about Excel:

http://www.mrexcel.com/

Thanks Dave!

I found that the answer to my question concerning your syntax lies in Boolean Logic.

In Boolean Logic, Excel treats "*" as meaning "AND", "+" means "OR". A "TRUE" result to a formula equals a "1" and a "False" result to a formula equals a "0".

In my days of working with old punched tape programmed computers like the ones I worked on in the Air Force in the early '70s, we called these "discrete" inputs and outputs (yes or no, true or false, high or low, on or off, etc.) all these outputs were either 1 or 0 which is the way these primitive computers operated. Everything was calculated using registers containing bytes (8 bit, 16 bit, etc., and you had Binary, decimal, hexadecimal, octal, etc. coding) of 1s and 0s (the 1s and 0s were actual outputs of physical, digital "gates" on the circuit boards, you had "And" gates, "OR" gates, "NAND" gates, "NOR" gates, etc.. Two inputs, 1 or 0, present at a gate resulted in one output, a 1 or 0 (High or Low, on or off). These outputs were combined and stored in registers that represented actual values, like airspeed, altitude, distance to target, trajectory, etc., etc. for weapons delivery systems that were handled by the airborne computer in the airplane to calculate the release point of a bomb, for example, or other navigational uses. The "black box" in the aircraft recorded all the vital states of the aircraft and the weapons delivery computer at weapons delivery time so that we, as technicians, could download them on the ground and figure out why our 500lb bomb missed the target by 200 ft.

You could represent any number with 1s and 0s (in Binary code, for example, the number 8 can be represented with a byte containing the Binary code: 1000
5 = 0101
3 = 0011
etc..

The "IF", "AND", and "OR", in traditional Excel statements, are not needed when Boolean Logic is applied. Thus the formula, =IF(And(A1>10,OR(B1=5,C1<3)),1,0) can be represented by: =(A1>10)*((B1=5)+(C1<3)),1,0)

Hope this helps others who were confused by your use of Boolean Logic in your formulas.

[Dave Schwartz]

Glad you finally got it.