Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! All results of my formula are the same! | Excel Discussion (Misc queries) | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
referencing cells that return blank results | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) |