What is the purpose of the " /10^10 " part of the formula?


T. Valko wrote:
You're welcome. Thanks for the feedback!

experience??

Yes, trial and error

Biff

"Matt" wrote in message
ups.com...
Awesome! Thank you. How in the world did you come up with this
formula? experience??

T. Valko wrote:
Try one of these:

Items = A1:A6
Numbers = B1:B6

Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER
(not
just enter):

=INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0))

Copy down to C6

Another way is to use column of helper cells and rank the numbers using a
tie breaker:

Enter this in C1 and copy down to C6:

=RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1

Then, in D1 copied down to D6:

=INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0))

Biff

"Matt" wrote in message
oups.com...
Say I have the following data:
items A through F have corresponding values
A 1
B 5
C 0
D 1
E 4
F 5

Is there a way to have excel output the items kind of like this:
large(range,1) -- B
large(range,2) -- F
large(range,3) -- E
large(range,4) -- A
large(range,5) -- D
large(range,6) -- C

as opposed to:
large(range,1) -- B
large(range,2) -- B
large(range,3) -- E
large(range,4) -- A
large(range,5) -- A
large(range,6) -- C

Thanks!