Thank you for your help, Miguel. > =MATCH(N(A2),'20120208'!B$2:B$1491,0)
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'!C$2:C$1491,0), MATCH(N(A2),'20120208'!D$2:D$1491,0), MATCH(N(A2),'20120208'!E$2:E$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 >> 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, >> > May be this is what you are looking for: > > =MATCH(N(A2),'20120208'!B$2:B$1491,0) > > Miguel Ángel. > -- For unsubscribe instructions e-mail to: [email protected] 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
