On 21 Jul, 19:28, "T. Valko" wrote:
The difference between our 2 formulas is that mine will account for empty
cells within the range. If there will not be any empty cells use DL's
formula.

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...



Disregard. I didn't have a column header when I tried that formula. If you
don't have a column header then you'd need to adjust for that.

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
=IF(COUNTA(AN:AN)7-ROWS($1:1)...

Need a slight tweak:

=IF(COUNTA(AN:AN)=7-ROWS($1:1)...

If there was a single entry you were not picking it up.

--
Biff
Microsoft Excel MVP

"daddylonglegs" wrote in message
...
Hello Neil,

making the same assumptions as Biff, i.e. that you have a header in AN1
and
data starts at AN2 try this formula copied down 5 more cells

=IF(COUNTA(AN:AN)7-ROWS($1:1),INDEX(AN:AN,MATCH(REPT("z",255),AN:AN)-6+ROW*S($1:1)),"-")

"T. Valko" wrote:

Hmmm...

I forgot to make one of the range references absolute.

...INDEX(AN2:AN100,LARGE...

The correct formula should be:

=IF(COUNTA(AN$2:AN$100)<7-ROWS($1:1),"-",INDEX(AN$2:AN$100,LARGE(INDEX((AN$*2:AN$100<"") *ROW(AN$2:AN$100),,1),7-ROWS($1:1))-1))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
.. .
That formula is flawed for a couple of reasons. It assumes there will
be
enough data to satisfy the result of the MATCH function. Also, even
if
there is enough data the result of the MATCH could easily return the
incorrect result because there will probably be many duplicates of
the
lookup_value and when used with a match_type argument of 0, will
always
match the first instance of the lookup_value.

That formula is basically looking for the last TEXT entry in the
column
and then returning the value that is offset from that location by -6
rows, -5 rows, -4 rows, etc.

Try this formula:

I'm assuming that AN1 is your column header and your actual data
starts in
AN2 on down.

=IF(COUNTA(AN$2:AN$100)<7-ROWS($1:1),"-",INDEX(AN2:AN100,LARGE(INDEX((AN$2:*AN$100<"")*R OW(AN$2:AN$100),,1),7-ROWS($1:1))-1))

Copy down to a total of 6 cells. Also, use a large enough range size
to
allow for newly added data. Or, you could use a dynamic range that
will
automatically adjust as you add new data.

How to create a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP

" wrote in
message
oups.com...
Hi

With some help I got a formula that works, but only after a fashion

Here was my original question:
Return last 6 results formula
I have a column in my sheet that fills in progressively over a
season
as team results are entered.

This particual column will show d for a goal-less draw (0-0), D for
a
score draw (1-1, 2-2 etc) and W or L for Win/Loss

I'd like to show the last 6 game 'form' in this format: W,L,W,W,d,D
using that column.
If less than 6 games I want it to put in dashes (-,-,W,L,d,D)

With kind help, this was the result:
=IF(INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN), AN:AN,
0)-6)=0,"-",INDEX(AN:AN,MATCH(LOOKUP(REPT("z",255),AN:AN),AN :AN,0)-6))

Next cell down has -5, next cell -4 etc etc
(A final cell concatenates to give the result I wanted)
What doesn't work is if there are less than 6 results I get #VALUE
as
the result

Can someone kindly
a) Possibly explain why
b) Explain what the formula actually does!

Thanks
Neil- Hide quoted text -

- Show quoted text -

Many thanks to you both.

Just one question with DL's method.
Which part of your formula do I change if data starts in row AE4?

Cheers
Neil