I have a spreadsheet as follows:
.....A....B........C.....D..E.......F........G.... ............H
...............................................low est........corresponding
1...'x'...'y'.......AR..............AR........'y'. ...............'x'
2...3....2.........1................1.........2... ..............* (see
below for formula)
3...2....3.........1................2.........3... ..............**
4...4....5.........1................3........etc.. ............***
5...5....4.........1
6...1....6.........1
7
8....'x'.....'y'....AR
9.....2......5......2
10....3.....6......2
11....1.....3......2
12
13....'x'...'y'....AR
14.....1....7......3
15.....3....8......3
16.....4....1......3
etc...etc...etc...etc
I have a spreadsheet with about 300 separate race events (with varied
numbers of competitors in each race) stacked vertically which I would
like to analyse separately. So I have manually added column 3 - which
allocates a separate array number to each separate race event and then
I have constructed a summary area (columns F, G and H above) that
summarises each race on consecutive rows.
I have written a formula for column F that automatically pulls out the
lowest 'y' {=(MIN(IF($C$2:$C$6000=ROWS($A$2:A2),$B$2:$B$6000) )))},
which works
Then I have tried to write formulae that pull out the corresponding 'x'
value (below), from looking up the lowest 'y' value:
*{=LOOKUP(G2,(IF($C$2:$C$6000=ROWS($A$2:$A2),$B$2: $B$6000)),$A$2:$A$6000)}
**{=LOOKUP(G3,(IF($C$2:$C$6000=ROWS($A$2:$A3),$B$2 :$B$6000)),$A$2:$A$6000)}
***{=LOOKUP(G4,(IF($C$2:$C$6000=ROWS($A$2:$A4),$B$ 2:$B$6000)),$A$2:$A$6000)}
etc
But these dont work and I cannot see why - can anyone else? Or is there
an easier way to do it? I am just about exhausted of attempts and
ideas...any help would be gratefully received...:)
--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
View this thread:
http://www.excelforum.com/showthread...hreadid=506313