# calc - offset named range

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.

`=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.

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`

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.

any alternatives to it? or other suggestions?

Resolve the concatenation.