Ask Your Question
0

calc - offset named range

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

DiesNuts 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 close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

Lupp gravatar image

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

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

edit flag offensive delete link more

Comments

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 +0200 )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 +0200 )edit

any alternatives to it? or other suggestions?

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

Resolve the concatenation.

Lupp gravatar imageLupp ( 2016-11-14 16:03:33 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

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

Seen: 348 times

Last updated: Nov 11 '16