Return last 6 results formula
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 |
Return last 6 results formula
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,B 1+2)&","&INDEX(A:A,B1+3)&","&INDEX(A:A,B1+4)&","&I NDEX(A:A,B1+5) Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro " wrote: 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 |
Return last 6 results formula
On 19 Mar 2006 07:26:37 -0800, "
wrote: 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 |
All times are GMT +1. The time now is 03:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com