Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

Thank you for your help, Miguel.


That is part of the problem.  That says within the WB1 column, that
there is a match on the 122 row, but the actual row is 123... so MATCH
gives a row relative to the top of the vector.  That is even better than
I was hoping for (I was thinking that I'd have to subtract a scalar
because the matrix started in Row 2 (or 3, since I'm trying to create
the stats before the draw on 2/8).
So, I dupe the MATCH function for each WB column, and can I then use MIN
to find the first occurrence...

Something like
=MIN( MATCH(N(A2),'20120208'!B$2:B$1491,0),
MATCH(N(A2),'20120208'!F$2:F$1491,0) )

That would give me the lowest row in the matrix where N(A2) is found...
I think.

Thank you for your help, Miguel.

Paz, (escribo español también)

Barry Smith
e bnsmith001[at]gmail[dot]com

MiguelAngel wrote:
El 12/02/12 0:37, Barry Smith escribió:
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 , most simply the GMO column
"number of games since last hit".

So far, I imported the current powerball winning numbers text file from

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

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

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

May be this is what you are looking for:


Miguel Ángel.

For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.