EXEL TECHNICAL PROBLEM

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
corbus
Posts: 149
Joined: Mon Aug 10, 2009 10:27 am

Lets pick up the three smallest values from array K9-K68

A=SMALL($K$9:$K$68,1) = 3.42 (found in position 9 of the array)
B=SMALL($K$9:$K$68,2) = 3.68 (found in position 4 of the array)
C=SMALL($K$9:$K$68,3)= 3.68 (found in position 11 of the array)
(let’s not worry about the syntax that would be to place the formula in the the right cell and that’s where the result goes)

So we have the smallest value of that range in A, the 2nd smallest in B and the 3rd smallest in C (B and C hold the same value 3.68).

Let’s now consider another formula that wants to pick up the corresponding position value from another array (T9-T67)

=INDEX($T$9:$T$67,MATCH(A,$K$9:$K$67,0)) – result 9
=INDEX($T$9:$T$67,MATCH(B,$K$9:$K$67,0)) – result 4
=INDEX($T$9:$T$67,MATCH(C,$K$9:$K$67,0)) – result 4

How can I tell the system that the 2nd 3.68 ( C ) is the third value and therefore it should pick up the corresponding number 11 and not twice 4?

I’ve thought of a brilliant solution but I thing it is not realizable: It were very elegant to be able to give the INDEX function the direction of the search, i.e. be able to say: "search from bottom up" and so it would encounter 11 before 4.
But I’d appreciate if anybody knows how to do that or any other idea on how to solve
The problem

Thanks
corbus
Posts: 149
Joined: Mon Aug 10, 2009 10:27 am

as it always happens, as soon as I had posted this message I thought of a very simple solution: it just needs to have both arrays also capsized and so be able to use them in both directions...

Incredibly simple... why took me so long?

Thanks anyway
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”