Dynamic Named Range last 12 values

[hp2108]

Hi,

I am trying to find the last 12 values (1 year) in a dynamic range that spans columns G through to IV. I have used offset functions that return a range that should span the last 12 values.

However, it is selecting the last 11 values and the first empty cell. (I want it to include the last 12 values)....I have tested the cell to ensure that it is in fact empty.

The formula is below:

=OFFSET(OFFSET(PVR!$G$1,0,COUNT(PVR!$G$1:$IV$1),1,1),0,0,1,-12)

Does anyone have any ideas - I have been looking at this for way too long. I need a fresh set of eyes!

any help is greatly appreciated - Thank You!

 

[hp2108]

I should add the range is contiguous and contains only numeric values.

The data currently ends at column AX but the named range selects AN:AY....

Thanks

 

[Sandeep Warrier]

Hi

This should give you the last 12 occupied cells range.

=INDEX(Sheet1!$1:$1,0,MATCH(9.999E+307,Sheet1!$1:$1)-11):INDEX(Sheet1!$1:$1,0,MATCH(9.999E+307,Sheet1!$1:$1))

 

[Marcelo Branco]

Hi,

Maybe this

=OFFSET(PVR!$G$1,0,COUNT(PVR!$G$1:$IV$1)-12,1,12)

HTH

M.

 

[Marcelo Branco]

Complementing my previous post

My formula works only if there is(are) no blank cell(s) in the middle of your data.

Sandeep's formula (i hadnt seen it) works even with blank cells, so its a safer (better) formula.

M.

 

[hp2108]

Thanks alot Sandeep - you have solved my problem with a completely different method to what i would have expected. Thanks!

Out of curiosity - do you know of any reasons as to why my original formula wouldn't have worked?

Thanks have a Great Day!

 

[hp2108]

Thanks Marcelo, yours is simpler! both work.

Given that the range is contiguous (and always will be) it doesn't really matter which one i use.

so not only have i solved my problem i have worked out a new way to approach the problem - Thanks Guys! (and thanks for tehe quick responses!)

 

[Marcelo Branco]

Out of curiosity - do you know of any reasons as to why my original formula wouldn't have worked?

Maybe it works with a small change
=OFFSET(OFFSET(PVR!$G$1,0,COUNT(PVR!$G$1:$IV$1)-1,1,1),0,0,1,-12)

M.

 

[Aladin Akyurek]

Hi,

I am trying to find the last 12 values (1 year) in a dynamic range that spans columns G through to IV. I have used offset functions that return a range that should span the last 12 values.

However, it is selecting the last 11 values and the first empty cell. (I want it to include the last 12 values)....I have tested the cell to ensure that it is in fact empty.

The formula is below:

=OFFSET(OFFSET(PVR!$G$1,0,COUNT(PVR!$G$1:$IV$1),1,1),0,0,1,-12)

Does anyone have any ideas - I have been looking at this for way too long. I need a fresh set of eyes!

any help is greatly appreciated - Thank You!

Define Lrec as referring to:

=MATCH(9.99999999999999E+307,PVR!$G:$G)

Assuming that the data starts at G2...

Define ICount as referring to:

=COUNT(PVR!$G$2:INDEX(PVR!$G:$G,ROWS(PVR!$G:$G)))

Define LastN as referring to:

=12

Assumin that PVR!$G$1:$IV$1 is numeric, define HSize as referring to:

=COUNT(PVR!$G$1:INDEX(PVR!$1:$1,COLUMNS(PVR!$1:$1)))

Finally...

Define Data as referring to:

=OFFSET(INDEX(PVR!$G:$G,Lrec),0,0,-MIN(LastN,ICount),HSize)