Dynamic Range using cells prior

[polarisking]

I want to capture a range N cells tall PRIOR to my selected cell. I would have thought I could do something like

OFFSET(AnchorCell,0,0,-N,1) and that would reverse the direction of the range. Doesn't work.

Any ideas? Thanks in advance.

 

[Mike86]

Are you using the OFFSET with something like SUM? If you just throw an array into a single cell, you'll get a #VALUE! error. Also, try putting the negative sign in the second field (OFFSET(Cell,-N,0,1,1)) That'll move you up two. Where you have it is trying to set a -2 cell range height.

 

[polarisking]

Mike, what I want is a range N cells high beginning N-1 above where my formula is.

 

[Mike86]

So you'd have OFFSET(Cell,-1*(N-1),0,N,0) You'll also need to watch and be below row N-1 from the top. This is a range/array, so use it in a SUM or similar statement.

 

[Khalid NGO]

Hi,

It is difficult to guess what you are looking for. Can you post a sample with some more detail what you are after / expected result etc?

Regards,

 

[r2c2]

So you'd have OFFSET(Cell,-1*(N-1),0,N,0) You'll also need to watch and be below row N-1 from the top. This is a range/array, so use it in a SUM or similar statement.

I think you can almost use Mike86's formula. Just change last 0 to 1.

Like this
=offset(cell, 1-n, 0, n, 1)
If you want to exclude the cell and grab previous n cells, then use
=offset(cell, -n, 0, n, 1)

 

[Mike86]

Damn typos...