calc - offset named range

[DiesNuts]

Let’s say I have a name (limited to the current sheet) dataX for range $Sheet1.$B$2:$B$11226

And I have names O2=1436 named start and O3=10260 named stop.

How do I now use these names to compute - for example - the average of the range dataX without the first start-1 values and without the values after the stop-th (i.e. in this example, $Sheet1.$B$1438:$B$10262 ).

Note: I could, of course, set the name dataX to just $Sheet1.$B$1 and then use OFFSET(dataX,start,0):OFFSET(dataX,stop+1,0), that’s not the solution I’m looking for.

[Lupp]

=AVERAGE(OFFSET(dataX;start-1;0;stop-start+1;1)) should do (if I understood correctly).

(Editing:)
The OQ seems to assume the OFFSET function is expecting a cell reference on its first parameter position. It will, however, also accept a range and will start with the topmost leftmost point (cell) of that range. The fourth and third parameter are to regard as newHeight and newWidth as they are actually named in the specification. Only if these parameters (or one of them) are omitted the respective oldHeight / oldWidth is of any meaning.

[DiesNuts]

this actually doesn’t seem to work.
I have a range OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($BD:$BD,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1), let’s name it dataX for consistency.

So =AVERAGE(OFFSET(dataX,0,1)) should give me the average of OFFSET($AB:$AB,start_,0,stop_-start_,1)~OFFSET($BE:$BE,start_,0,stop_-start_,1)~OFFSET($CH:$CH,start_,0,stop_-start_,1), right?

What I instead am getting is Err: 504

[Lupp]

OFFSET expects a reference as its first parameter. If restricted to a single sheet a reference is describing a rectangular cell range, otherwise a cuboid. By the specification of OFFSET the case of a cuboid is not explicitly excluded. However, handling cuboids intersecting more than one sheets is not implemented.
Anyway, your named expression ‘dataX’ is not a reference at all but a ‘Reference Concatenation’ aka ‘Union’, resulting in a ‘Reference List’. OFFSET cannot accept it.

[DiesNuts]

any alternatives to it? or other suggestions?

[Lupp]

Resolve the concatenation.