0

I have a table returned from an SQL query that contains various column of values held in an array format (e.g. ={1,2,3}). Due to the information stored within, each array varies in size and is not consistent. I need to be able to take this column of arrays and use them to perform functions such as AVERAGE or MEDIAN on their values in totality.

Originally, I thought I could simply select the range and that would work, however that only gives me a result based off the visible value of the cell (the first value in the array), or a Spill error if I've messed with the values in that column and caused excel to recognize the cell as an array.

How can I turn these arrays into a usable array for me to use formulas on?

2
  • A single cell cannot hold an array. Even with Office 365 each cell in the spill holds one value. You can use the # suffix on the first cell to refer to the spilled range, but again each cell will hold one value. Commented Oct 5, 2021 at 17:50
  • Oof, that hurts, going to have to submit a request for a different output then.
    – willuwontu
    Commented Oct 5, 2021 at 18:04

1 Answer 1

0

What you describe is absolutely possible. (Unless I greatly misunderstand your description.)

Your table is populated with a column of cells containing the arrays described. Presumably you are considering the table to be that column (of course!) and all the columns it fills spreading out the array in each row. Let's say the column is column A and the longest array is a 25 element array so that row extends to include displaying a value in column X. (Note: just picking a column here. It could have any number of elements Excel supports. A little about that later.) If you have 35,000 rows then, the table populates the range A1:X35000 for our purposes.

You cannot EASILY address that range inside a single formula. You CAN do it, but an easier method with a couple variations is the way to go. So, those and then some other ways:

  1. Just refer to that range in your formula. Simple as that.

  2. Pick a "helper region"... perhaps a separate worksheet dedicated to this purpose, or to be analogous to what is called a "helper column" this would be a "helper sheet." Simply type =A1:X35000 in cell A1 and an array springs to life that includes ALL of your input data's material in the same for it displays in BUT this is a single array SPILL-ing there and can be address directly with any operation you care to perform with it. You'd type some address like "Sheet2!A1#" using the real sheet name and cell.

  3. Create a Named Range and in its Refers to box, give the address "A1:X35000". The same abilities as in 1., above, now exist, but there is no helper region.

  4. Use LET() and define the range with a name and the "A1:X35000" address: =LET( Range,A1:X35000, MEDIAN(Range) ).

Given the ease of these, the harder methods don't bear thinking about.

I experimented with a 39,312 row set of three element arrays to do each of the above and all were successful: so there are no obvious limits here for size of arrays used by Excel that might relate to cell string length limits as all of those top out at 32,767 characters and I used four-ish times as many.

The essence here is you have a series of arrays and cannot use them directly they way you need to. It seems likely you are using an addressing of the range such as (using the same example) "A1:A35000" or "A1#". The former will only get the first element of each array, the same way "B1:B35000" would select all the second elements, and so on (the latter gives only the first cell's data). Excel places each array utterly FULLY IN the cell it is in. So, place one of the arrays in A1 and while it displays as being in A1:A3, it is fully stored as the content of A1. However, to make the functionality directly available without an intermediate level of functions, Excel handles addressing as the cells what you wish to address are where the data really is. So all the intermediate work is "under the hood."

This means you get the situation described. All the data is IN "A1:A35000" but addressing must be done where it displays ("A1:X35000").

And as noted, each cell is its own linear array, not an array element in a larger, two dimensional array. You have to create the larger array by addressing the entire block of cells the display range covers.

Moving on... you can apply all the techniques you know or can find for making your range address dynamic. Just you have to be trickier with some. For instance, Excel treats the elements as being in the display cells for purposes of formulas, hence the addressing, above. That also means you can directly access the actual content of, say, A1. But it is a formula, so FORMULATEXT() works on it. This means a formula that takes that output and finds its LEN(), then subtracts from that the length of its formula's string after applying SUBSTITUTE() on it to remove the commas, plus 1, gives you the number of elements which is also the number of columns it displays over. So doing that to column A and wrapping it with MAX() tells your FORMULA how wide the table of displayed results is. So, try a technique to learn what you want and if it fails, try it on FORMULATEXT().

If you can have a different output, that would certainly be a way to go. Normally the best way in cases like these. But it is very, very easy and "clean" to deal with as is and get what you want. You just need that middle step to change the column of linear arrays into a single 2-D array.

1
  • The issue is the array is in column A, and then in column B, I have a set of strings. So the array from column A isn't spilling over into column B as column B has values completely unrelated to column A. Additionally, I have multiple of these array columns, intermingled with single value columns.
    – willuwontu
    Commented Oct 6, 2021 at 14:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .