[libreoffice-users] Spreadsheet -- General searching guidance...

I have always been fascinated by statistics.

I'm trying to understand the math behind powerball statistics, but my
spreadsheet experience is having trouble meeting the bill.

I'm trying to duplicate the statistics that I saw on
http://www.simplypowerball.com/?q=node/33 , most simply the GMO column
"number of games since last hit".

So far, I imported the current powerball winning numbers text file from
http://www.powerball.com/powerball/winnums-text.txt

After import, the sheet '20120208' contains draws including 20120208 and
has 8 cols from the original data--
A "DrawDate",
B "WB1",
C "WB2",
D "WB3",
E "WB4",
F "WB5",
G "PB",
H "PP",
and I added a column I "Draw DOW",
which is simply a DOW number from the draw date in Col A.  The value is
either 4 for Wed, and 7 for Saturday. Plan to use that info later somehow.

Next, I added another sheet which will scan the draws from first sheet,
and report back the same stats from the SimplyPowerball webpage.

The first task is getting the GMO column working.  In order to do that,
I need to understand how to lookup data in an array, report the row, and
then to subtract a constant.
Since the last draw is in row 2, I think I need to subtract 1.  Yet I
digress.

> From sheet 'Pre-2008 WB' I'm trying to figure out how to scan array
'20120208'.B3:'20120208'.F1491 row-by-row, and find the first row with a
number that I'm looking for (which is in 'Pre-2008' in column A, which
contains the sequential list of white balls 1 thru 55).

I keep getting an error 502... which doesn't tell me what part of my
function is invalid.
I have tried LOOKUP function, MATCH function, and currently I am trying
the HLOOKUP function.
The current cell has the formula
[=HLOOKUP(N(A2),'20120208'.B3:'20120208'.F1491,0)] .
A2 contains the number 1, so I'm attempting to just find that a 1 has
ever been drawn at the moment.
Ideally, the formula should contain a ROW function (I think), and
subtract 2...
to show me before 2/8, when was a 1 drawn as a whiteball.

I have thrown a copy of my current spreadsheet onto the web on my
GoogleDoc account. the file link is http://bit.ly/wjJtXu .
I will continue to fiddle with the formula, but if someone would be so
kind as to teach me a little about how to find the row of the last time
that a number was drawn... I would be grateful... because the error from
Libre office doesn't really point me in a direction to fix the error. :)

Thank you,

-- 
Barry Smith
e bnsmith001@gmail.com


-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted