MiguelAngel wrote: > El 12/02/12 16:04, Barry Smith escribió: >> 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) >> > For you can use a matrix fomula: > =ArrayFormula(MIN(IF('20120208'!B$2:H$1490=$A2;ROW('20120208'!B$2:H$1490);999999))) > > This is how is view in google docs, > > To enter in LibreOffice, intruduce the formula: > =MIN(IF('20120208'.B$2:H$1490=$A2;ROW('20120208'.B$2:H$1490);999999)) > and finish with Crtl+Shift+Enter > > Take care of sheet separator, if you use Libo or google docs. > > Miguel Ángel. > > Wow!
I read about array functions somewhere in the LO docs, but I had no idea how to use them. Also, I'm only using LO. I tossed the xls up to Google docs so that people who wanted to help could see the sheets that I was working with. > To enter in LibreOffice, intruduce the formula: > =MIN(IF('20120208'.B$2:H$1490=$A2;ROW('20120208'.B$2:H$1490);999999)) > and finish with Crtl+Shift+Enter I'll have to study that formula carefully... =MIN(IF('20120208'.B$2:G$1490=$A2,ROW('20120208'.B$2:G$1490)," ")) Column H is the PowerPlay column - it doesn't fit into the domain for Powerball Draws... and the Ctrl+Shift+Enter... I tried just Enter and got an Err: 504. I copied the formula for B to G, pasted, and finished with Ctrl-Shift-Enter, and it worked. Thank you for your help, Miguel. I need to learn more about "array" functions... the one applied when you use the Ctrl-Shift-Enter. Back to the design table. :) Paz, -- Barry Smith e bnsmith...@gmail.com w http://bit.ly/l8QJup -- For unsubscribe instructions e-mail to: users+h...@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