If in a formula .. or a better way?

[Box666]

I have a formula that lookup the last 13 rows of a spreadsheet.

=OFFSET($A$2;COUNTA(A:A)-ROW(A1)-1;0)
to
=OFFSET($A$2;COUNTA(A:A)-ROW(A13)-1;0)

This works great, so long as there are at least 13 moths of data, if
not then it shows the column header"Month" or "#REF!".

I thought I could use a "IF" statement, so if the result was "Month"
or "#REF!" then it would produce a " " but I cannot seem to get "IF"
to work with the above formula.

Can anybody help with this, or is there a better way to amend the
above formula if there are not 13 months of data.

With thanks

Bob

 

[Guest]

Try:

=OFFSET($A$2;MAX(0,COUNTA(A:A)-ROW(A13)-1);0)

 

[Roger Govier]

Hi

One way
=OFFSET($A$2;MAX(1;COUNTA(A:A)-ROW(A13)-1);0)

Incidentally, if you are trying to pick up a rolling 12 months worth of
data, it should be ROW(A12) not (A13)

 

[Box666]

Thank you but this still picks up the last row of data, so in my case
I have 8 rows of correct data plus 1 row repeated 5 times as I intend
to use this in a chart it will make it look very strange.

Is there a way to get a null returned instead of tthe last row
repeated. Am I back to the "IF" option ?

Bob

 

[Roger Govier]

Hi

With Date in cell A1 and 01 Jan 07 to 01 Aug 07 in cells A2 to A9
I put this formula in cell L2 and copied down
=IF(OFFSET($A$2,MAX(1,COUNTA(A:A)-ROW(A1))-1,0)=L1,
NA(),OFFSET($A$2,MAX(1,COUNTA(A:A)-ROW(A1))-1,0))

This gave 01 Aug 07 in L2 through 01 Jan 07 in cell L9 and
#N/A for all cells below this.

A chart will recognise #N/A as no data available for that period.

 

[Guest]

How are you using these formulae to get your data range, as I don't
understand how you get your five repeated rows.

Can you give an example with sample data/expected results please?

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,

 

[Box666]

Roger,
I have spent quite a while trying to utilise your formula. Whilst
it works on cells as detailed.... however if a cell has a "0" in it,
it is shown as NA. Also if there are two cells next to each other with
the same data in them, then everything following is marked as NA.
Is there any way to avoid this happening.

Bob

 

[Roger Govier]

Hi Bob

Modifying the formula in cell L3 onward to
=IF(OFFSET($A$2,MAX(0,COUNTA(A:A)-ROW(A2))-1,0)>L2
,NA(),OFFSET($A$2,MAX(0,COUNTA(A:A)-ROW(A2))-1,0))
seems to get round the problem of two successive cells having the same
date

If I don't have the word "Date" (without the quotes) written in cell A1
then I can get problems.
If this is missing, then the COUNTA(A:A) parts of the formula will
return the wrong value.
Maybe you didn't type Date in A1?

If a cell has "0" is another problem.
Are you saying your data will have zero's or will the cells be blank?
If the cells are formatted as Date and have a "0" in them, then this
will show as "00/01/1900"
If this is the case, then the revised test as above fails because the
date is not greater than the corresponding value being looked up.

I had imagined from your original description, you had a series of
successive months of data, from which you were wanting to extract the
last 12 months, but if there was not 12 months worth of data, then
return NA() rather than 0 for those months so your chart would work. Is
this not the case now?