Ask Your Question

calc - offset named range [closed]

asked 2016-11-11 18:36:08 +0100

DiesNuts gravatar image

updated 2020-08-07 20:20:28 +0100

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-07 20:20:54.136832

1 Answer

Sort by » oldest newest most voted

answered 2016-11-11 18:42:18 +0100

Lupp gravatar image

updated 2016-11-11 18:54:45 +0100

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

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.

edit flag offensive delete link more


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

DiesNuts gravatar imageDiesNuts ( 2016-11-14 11:17:27 +0100 )edit

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.

Lupp gravatar imageLupp ( 2016-11-14 15:59:04 +0100 )edit

any alternatives to it? or other suggestions?

DiesNuts gravatar imageDiesNuts ( 2016-11-14 16:01:34 +0100 )edit

Resolve the concatenation.

Lupp gravatar imageLupp ( 2016-11-14 16:03:33 +0100 )edit

Question Tools



Asked: 2016-11-11 18:36:08 +0100

Seen: 634 times

Last updated: Nov 11 '16