[Solved] Syntax for formulaic "array" inside single cell

[Bald Eagle]

I have some quantities in 10 columns (7 shown)
24 64 44 48 44 40 11
I have locations in the columns to the right of that
26 62 63 64 74 76 83

I want to RANK() the quantities in ascending order
2 7 4 6 4 3 1 #VALUE! #VALUE! #VALUE!

and then use that ranking to INDEX() the locations - thus ranking the locations by the quantities they hold.
83 26 76 63 #N/A 64 62 #N/A #N/A #N/A

When I right fill the rows, the column number automatically increments to give me 10 cells - an "array" of values.
Is there a way to do the same inside a formula without having to hand-code the values for the {a, b, c, d, e, ... } style array?

The idea being to do something like: =INDEX($L2:$U2;1; MATCH(COLUMN(U3)-11; RANK ARRAY HERE]; 0) )

Is there a way to name a range of cells like ARRAY1, and then copy that down so that they become ARRAY2, ARRAY3, ARRAY4....?

I'm thinking I'm asking the impossible again.

[keme]

Perhaps you want the SMALL() or LARGE() function.
To pick a numbered item, in ascending order of size, from an unsorted list:

Code: Select all

=SMALL(<range>;<number>)

For descending order (largest is number 1), use the LARGE() function instead.

[Bald Eagle]

Yes!
Excellent. That will do exactly what I want.

I wrote it up in a little sheet to share the fully implemented solution.
Sometimes trying to find the right function is a challenge, but those are wonderful little tool!

Thank you

[Bald Eagle]

So - there's one small problem with the way SMALL() or LARGE() works - let's suppose I have the same quantities in both Loc1 and Loc 7.
These functions will return the same number, and so the same location for the cross-indexed locations.
So if they both have QTY = 1, then I get Loc1, Loc1 instead of Loc1, Loc7.

Can anyone suggest a fix?

[keme]

You can add a "fudge factor" for the amount at each location to act as a tiebreaker. The fudge factor must be large enough so it is not eliminated by rounding, but small enough that it won't affect ordering (only affect elements where amount is equal). For a fudge factor to satisfy the last condition, it must be smaller than <measure resolution>/<element count>. IOW, if you unit is kg and you measure to the nearest gram, the measure resolution is 0,001. With 20 different locations, the safest fudge factor would be 0,001/20=0,00005

Add a helper "sort row" where you add (<location#> * <fudge factor>) to the measurement. Use that to rank your locations. That will eliminate ties. The values in the sort row must only be used for sort/lookup. Actual values (unfudged) must be used in calculations.

[Bald Eagle]

Excellent. Thanks, keme.
I was mulling this over on my ride to lunch, and I came up with something almost identical.
Add a random value between 0.11 and 0.99 and then floor() or otherwise round the quantity for display/use.

I thought for sure I had massively botched some 12-layer-deep chain of values and cell references and calculations, but I debugged it on a very small scale, and isolated the real issue.

Thanks!

[Bald Eagle]

So, almost there, but having a bit of trouble with the implementation of the solution.

I'd like to do this without helper rows, as the spreadsheet is ... large enough already.

I'd like to simply take one array and add it to another, or more accurately, obtain a new array whose sequential elements are individually the sum of the sequential elements of the argument arrays.

So {a,b,c,d,e,f} + {1,2,3,4,5,6} would return {a+1, b+2, c+3, d+4, e+5, f+6}

Interestingly enough, I tried
=SUM(S52:AB52+S53:AB53)
and this gives me -- party desired, and partly unexpected results.

First, it does NOT give the sum of S52:AB52 plus the sum of S53:AB53.
Instead, it gives S52+S53 :O
Filling columns to the right gives me the entire desired sequence.

I would however, like to do something more like:
Array 2 = 0 1.0001 1.0002 1.0003 1.0004 1.0005 1.0006 1.0007 1.0008 1.0009

=SMALL(INDIRECT($V10)+Array2; COLUMN(G10)-6)


Is there a way to do this?

[Lupp]

(Just for completeness.)
The solution below should work if the IFERROR() function is available.

Code: Select all

=IFERROR(INDEX($L7:$U7;MATCH(SMALL(IFERROR($B7:$K7+COLUMN($B7:$K7)/10000;10000000000);COLUMN(V7)-COLUMN($V7)+1);$B7:$K7+COLUMN($B7:$K7)/10000;0));"")

The disambiguation suggested already by "keme" is implemented with a "fudge" addend most likely meeting the needs if applied with not too large integer quantities.
However, the SMALL() function has to work through the same array again and again. Thus the solution is a crunch anyway. Since there is no sorting by a single standard function, there will hardly be a rescue without resorting to user functions.

Edit 1:
Since I recently (again) wrote a function for sorting data by a specific proceeding (first determining the needed permutation and then applying it going through its cycles) I got the idea to accelerate the creation of an anyway planned variant and to apply it to the current task. It was easier than I had thought.

The result is demonstrated in the attached example. Of course, it can only work if the execution of the included "macro" is permitted. You should probably first study the code and move it to your standard library if you feel sure it is safe.

Please note that I see sorting in and by columns as the standard in spreadsheets. To avoid complications with too many alternatives controlled by parameters I therefor specialised the mentioned function on columns and top-down arrays of rows. This requires to apply the standard function TRANSPOSE() twice in addition if sorting shall be done left to right.