How to INDEX(INDIRECT(cell1)~INDIRECT(cell2),,col)?

[greg_h2os]

The reference concatenation operator “~” probably works fine for literal ranges. But my ranges are referenced by defined names. How can I join two named ranges into one range-list argument? How can I join two INDIRECT ranges where I’m calculating the range names with CONCATENATE?

I have an array of data that is sorted by a grade column, then by an hours column. I defined a name for each block of data with the same (grade, hours) values. So I have dozens of named ranges within the data array. That’s fine for taking AVERAGE, MAX, MIN, PERCENTILE, etc., of an INDEX column in one named range.

I also need to lump some outlier ranges together, so I want to join a couple of named ranges (e.g., for 5.0 and 5.5 hours) without disrupting the systematic range names.

Since I’m calculating the range names with CONCATENATE, what I want to join with tilde is INDIRECT() ranges. Is there no way for INDIRECT(cell) to be used in place of $C$2:$C$12 in a list of ranges joined with “~”?

• Correct (721): =SUM($Data.$F$6:$F$12)
• Correct (721): =SUM(INDEX(Gr12_Hrs5_0,6))
• Correct(1252): =SUM($Data.$F$6:$F$12~$Data.$F$13:$F$17)
• Wrong (721): =SUM(INDEX(Gr12_Hrs5_0~Gr12_Hrs5_5,6))
• Wrong (721): =SUM(INDEX(INDIRECT($B$5)~INDIRECT($B$6),6))

[greg_h2os]

I think I get it. ROWS(ref1~ref2) and INDEX(ref1~ref2,col) do not work and they fail to return an error. They don’t work because “~” doesn’t define whether to “stack” the references as more rows, or as more columns. So the total dimensions (rows, cols) of a (ref1~ref2) list is undefined, and INDEX() can’t work on that. It should return an error code. Here are formulas that work.

• Correct(1252): =SUM(INDEX(Gr12_Hrs5_0,6)~INDEX(Gr12_Hrs5_5,6))
• Correct(1252): =12*AVERAGE(INDEX(Gr12_Hrs5_0,6)~INDEX(Gr12_Hrs5_5,6))
• Correct(1248): =12*PERCENTILE(INDEX(Gr12_Hrs5_0,6)~INDEX(Gr12_Hrs5_5,6),0.5)

[mariosv]

I think you can’t.

On other hand, forgive me but seems a bit complicated define ranges if there is a tabular data, to obtain Average and other values.

Proper work for do with Pivot table.

Even with formulas without any range defined for groups, with SUMPRODUCT() function or other functions in arrays formulas.