Hstack and Rank.. incompaible??

[MartyCollins]

Hi there,
So i have a couple of spilled arrays side by side that have been working fine for the last 2 years or so.
So to take advantage of Hstack i am trying to put them all into one formula.
They all stack together fine except the final column which is a spilled rank of the adjacent column.

Its sensitive data so here is a really simple Example to exlpain:
On the left.. 2 spilled arrays, on the right Hstack brininging the 2 arrays together (My real data has several complex arrays and they stack together fine..except for the rank!)
Thanks,
Martin.

Lamdbas.xlsx
	R	S	T	U	V
4	Individual Spilled Arrays			Hstack
5	Random	Rank		Random	Rank
6	41	11		94	#VALUE!
7	1	1		61	#VALUE!
8	49	13		5	#VALUE!
9	89	19		43	#VALUE!
10	86	18		64	#VALUE!
11	68	15		63	#VALUE!
12	10	5		3	#VALUE!
13	38	10		45	#VALUE!
14	28	7		30	#VALUE!
15	6	3		72	#VALUE!
16	7	4		39	#VALUE!
17	31	9		45	#VALUE!
18	30	8		71	#VALUE!
19	73	16		1	#VALUE!
20	5	2		68	#VALUE!
21	46	12		20	#VALUE!
22	90	20		44	#VALUE!
23	49	13		85	#VALUE!
24	25	6		79	#VALUE!
25	77	17		17	#VALUE!
Goodly_matchTax

Cell Formulas
Range	Formula
R6:R25	R6	=RANDARRAY(20,1,1,100,TRUE)
S6:S25	S6	=RANK.EQ(R6#,R6#,1)
U6:V25	U6	=LET( Randoms, RANDARRAY(20,1,1,100,TRUE), Priority, RANK.EQ(Randoms,Randoms,1), Hstack(Randoms,Priority) )
Dynamic array formulas.

 

[MartyCollins]

The weird thing is this works...

=LET( Randoms, R6#,
Priority, RANK.EQ(Randoms,Randoms,1),
Hstack(Randoms,Priority) )

 

[Fluff]

Rank only works on a range, not an array which is why your original formula does not work.
How about

=LET(Randoms,RANDARRAY(20,1,1,100,TRUE),Priority,MATCH(Randoms,SORT(Randoms),0),HSTACK(Randoms,Priority))

 

[Peter_SSs]

Rank only works on a range, not an array

Practically, that seems to be the case, but it isn't what the Help is saying is it?

 

[Fluff]

What I originally meant (but didn't say very well) is that the 1st argument has to be a range.
That said I subsequently tried to use BYROW to show another option & found it didn't work with an array as the 2nd argument either, so the help is definitely missleading (unless I'm doing something wrong).

 

[Peter_SSs]

the 1st argument has to be a range.

The Help does not specify that as a range either - unlike, for example, the SUMIFS help syntax

 

[Fluff]

And even that is misleading as you can use an array for the criteria.

 

[MartyCollins]

Thanks Fluff/Peter- ill have to try get my head around that as it accepts a spilled array as an input... anyway that solution works.. i just have to try shoehorn it into my own work!!

 

[Peter_SSs]

ill have to try get my head around that as it accepts a spilled array as an input

Well, that is a range. In your working example, R6# is the worksheet range of cells beginning at cell R6 and ending at the last spilled cell.

 

[MartyCollins]

Well, that is a range. In your working example, R6# is the worksheet range of cells beginning at cell R6 and ending at the last spilled cell.

Ok that helps with my understanding of Range V Array.