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

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<"")*RO W(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
ups.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

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<"")*RO W(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
ups.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

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+ROWS($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<"")*RO W(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
ups.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

=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+ROWS($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<"")*RO W(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
ups.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

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+ROWS($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<"")*RO W(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
ups.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

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+ROWS($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<"")*RO W(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
ups.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

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

After some more tweaking I was able to shave a few more keystrokes off the
formula.

Assumptions:

no empty cells within the range
there will be no row insertions before row 1

=IF(COUNTA(AN:AN)<ROWS(1:$7),"-",INDEX(AN:AN,COUNTA(AN:AN)-ROWS(1:$6)+1))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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+ROWS($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<"")*RO W(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
ups.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

Hello Neil

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

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100)-ROWS(1:$6)+1))

"T. Valko" wrote:

After some more tweaking I was able to shave a few more keystrokes off the
formula.

Assumptions:

no empty cells within the range
there will be no row insertions before row 1

=IF(COUNTA(AN:AN)<ROWS(1:$7),"-",INDEX(AN:AN,COUNTA(AN:AN)-ROWS(1:$6)+1))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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+ROWS($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<"")*RO W(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
ups.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

On 22 Jul, 00:28, daddylonglegs wrote:
Hello Neil

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

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100*)-ROWS(1:$6)+1))



"T. Valko" wrote:
After some more tweaking I was able to shave a few more keystrokes off the
formula.

Assumptions:

no empty cells within the range
there will be no row insertions before row 1

=IF(COUNTA(AN:AN)<ROWS(1:$7),"-",INDEX(AN:AN,COUNTA(AN:AN)-ROWS(1:$6)+1))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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 -

Hmm. The formulae don't seem to work.
I will have data that will progressively fill AE4 to AE49 (there are
46 games per season), so I changed the formula to =IF(COUNTA(AE$4:AE
$49)<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
and copied down 6 times.
but i get a blank results
I'll explain what happens in the Cells AE4 to 49 and perhaps this
affects the results?
Formula in Cell AE4 is
=IF(AD4="","",IF(AA4AB4,"W",IF(AA4<AB4,"L",IF(AA4 =AB4,IF(AA4=0,"d","D"))))),
copied down to AE49
So, as results are filled in week by week, AE4 to 49 will hold the
result W, D, d or L

Cells AE2 and AE3 are blank, whilst AE1 is part of a merged cell
across X1-AW1
If I progressively evaulate the formula I get
IF (46<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-
ROWS(1:$6)+1))
IF (47<7,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,46-6+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,40+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,41))
IF (FALSE,#N/A,$AE$44)

Baffled!
Neil

" wrote in message
ups.com...
On 22 Jul, 00:28, daddylonglegs wrote:
Hello Neil

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

Try using Biff's latest suggestion amended to this.....

=IF(COUNTA(AE$4:AE$100)<ROWS(1:$6),"-",INDEX(AE$4:AE$100,COUNTA(AE$4:AE$100*)-ROWS(1:$6)+1))



"T. Valko" wrote:
After some more tweaking I was able to shave a few more keystrokes off
the
formula.

Assumptions:

no empty cells within the range
there will be no row insertions before row 1

=IF(COUNTA(AN:AN)<ROWS(1:$7),"-",INDEX(AN:AN,COUNTA(AN:AN)-ROWS(1:$6)+1))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
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 -

Hmm. The formulae don't seem to work.
I will have data that will progressively fill AE4 to AE49 (there are
46 games per season), so I changed the formula to =IF(COUNTA(AE$4:AE
$49)<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
and copied down 6 times.
but i get a blank results
I'll explain what happens in the Cells AE4 to 49 and perhaps this
affects the results?
Formula in Cell AE4 is
=IF(AD4="","",IF(AA4AB4,"W",IF(AA4<AB4,"L",IF(AA4 =AB4,IF(AA4=0,"d","D"))))),
copied down to AE49
So, as results are filled in week by week, AE4 to 49 will hold the
result W, D, d or L

Cells AE2 and AE3 are blank, whilst AE1 is part of a merged cell
across X1-AW1
If I progressively evaulate the formula I get
IF (46<ROWS(1:$7),"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-
ROWS(1:$6)+1))
IF (47<7,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,"-",INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,COUNTA(AE$4:AE$49)-ROWS(1:$6)+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,46-6+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,40+1))
IF (FALSE,#N/A,INDEX(AE$4:AE$49,41))
IF (FALSE,#N/A,$AE$44)

Baffled!
Neil

Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!

--
Biff
Microsoft Excel MVP

Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK*(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Many thanks Biff. It's now perfect.

Thanks for persevering with me!

Neil
In soggy central England.

" wrote in message
ups.com...

Ah, I see! That's because the cells are *never* empty. The formulas in
AE4:AE49 return either one of the letters or a formula blank which is an
empty TEXT string. Even though the cell looks empty it isn't and the
COUNTA
function can "see" those formula blanks.

So, try this:

=IF(46-COUNTBLANK(AE$4:AE$49)<ROWS(1:$6),"-",INDEX(AE$4:AE$49,46-COUNTBLANK*(AE$4:AE$49)-ROWS(1:$6)+1))

That should straighten things out!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Many thanks Biff. It's now perfect.

Thanks for persevering with me!

Neil
In soggy central England.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP