|
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
=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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
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 |
Help understanding what a formula does and why it's 'faulting'
" 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 |
Help understanding what a formula does and why it's 'faulting'
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. |
Help understanding what a formula does and why it's 'faulting'
" 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 |
| All times are GMT +1. The time now is 10:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com