Return last 6 results formula

[neil40]

Hi

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
Any suggestions gratefully received

Thanks in advance
Neil

 

[Ron Coderre]

Try something like this:

Single cell approach:
For a list of outcomes in A1:A100
B1:
=INDEX(A1:A100,MATCH(REPT("z",255),A:A)-5)&","&INDEX(A1:A100,MATCH(REPT("z",255),A:A)-4)&","&INDEX(A1:A100,MATCH(REPT("z",255),A:A)-3)&","&INDEX(A1:A100,MATCH(REPT("z",255),A:A)-2)&","&INDEX(A1:A100,MATCH(REPT("z",255),A:A)-1)&","&INDEX(A1:A100,MATCH(REPT("z",255),A:A))

Somewhat cleaner 2-cell approach:
B1: =MATCH(REPT("z",255),A:A)-5
C1:
=INDEX(A:A,B1)&","&INDEX(A:A,B1+1)&","&INDEX(A:A,B1+2)&","&INDEX(A:A,B1+3)&","&INDEX(A:A,B1+4)&","&INDEX(A:A,B1+5)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Ron Rosenfeld]

Hi

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
Any suggestions gratefully received

Thanks in advance
Neil

Assume the result is in A1 and the scores are in A2:An

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use the formula:

=MCONCAT(OFFSET(A1,COUNTA(A2:A100),,-6),",")




--ron