Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last, number in a column, 2nd last etc.
I have a spreadsheet which contains months of data for golfers. Some play
every saturday, some play when they are here on holiday. For months I have input data and left blanks when the players were not here. This worked until yesterday when someone asked, can we see each players last 6 weeks results when they were playing. The formula I use for last result is =IF(INDEX(C:C;MATCH(99999999999;C:C));(INDEX(C:C;M ATCH(99999999999;C:C)))) and for the next I use=IF(INDEX(C:C;MATCH(99999999999;C:C)-1);(INDEX(C:C;MATCH(99999999999;C:C)-1))). The First bit was ok but the next bit showed a blank if it was their week off. I want the blanks ignoring, and numbers only displayed. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last, number in a column, 2nd last etc.
Try this
=INDEX(C1:C1000,LARGE(IF(C1:C1000<"",ROW(C1:C1000 )),2)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Change the ,2 for the 3rd from last etc. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dartanion" wrote in message ... I have a spreadsheet which contains months of data for golfers. Some play every saturday, some play when they are here on holiday. For months I have input data and left blanks when the players were not here. This worked until yesterday when someone asked, can we see each players last 6 weeks results when they were playing. The formula I use for last result is =IF(INDEX(C:C;MATCH(99999999999;C:C));(INDEX(C:C;M ATCH(99999999999;C:C)))) and for the next I use=IF(INDEX(C:C;MATCH(99999999999;C:C)-1);(INDEX(C:C;MATCH(99999999999;C:C)-1))). The First bit was ok but the next bit showed a blank if it was their week off. I want the blanks ignoring, and numbers only displayed. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last, number in a column, 2nd last etc.
Thanks Bob,- when I copied it and Ctrl-Shift-Entered it , I got an error
message, and the C1000,LARGE highlighted. Any suggestions as to why? "Bob Phillips" wrote: Try this =INDEX(C1:C1000,LARGE(IF(C1:C1000<"",ROW(C1:C1000 )),2)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Change the ,2 for the 3rd from last etc. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dartanion" wrote in message ... I have a spreadsheet which contains months of data for golfers. Some play every saturday, some play when they are here on holiday. For months I have input data and left blanks when the players were not here. This worked until yesterday when someone asked, can we see each players last 6 weeks results when they were playing. The formula I use for last result is =IF(INDEX(C:C;MATCH(99999999999;C:C));(INDEX(C:C;M ATCH(99999999999;C:C)))) and for the next I use=IF(INDEX(C:C;MATCH(99999999999;C:C)-1);(INDEX(C:C;MATCH(99999999999;C:C)-1))). The First bit was ok but the next bit showed a blank if it was their week off. I want the blanks ignoring, and numbers only displayed. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last, number in a column, 2nd last etc.
I forgot to change it to your system separator before posting (I have
English Excel). Try =INDEX(C1:C1000;LARGE(IF(C1:C1000<"";ROW(C1:C1000 ));2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dartanion" wrote in message ... Thanks Bob,- when I copied it and Ctrl-Shift-Entered it , I got an error message, and the C1000,LARGE highlighted. Any suggestions as to why? "Bob Phillips" wrote: Try this =INDEX(C1:C1000,LARGE(IF(C1:C1000<"",ROW(C1:C1000 )),2)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Change the ,2 for the 3rd from last etc. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dartanion" wrote in message ... I have a spreadsheet which contains months of data for golfers. Some play every saturday, some play when they are here on holiday. For months I have input data and left blanks when the players were not here. This worked until yesterday when someone asked, can we see each players last 6 weeks results when they were playing. The formula I use for last result is =IF(INDEX(C:C;MATCH(99999999999;C:C));(INDEX(C:C;M ATCH(99999999999;C:C)))) and for the next I use=IF(INDEX(C:C;MATCH(99999999999;C:C)-1);(INDEX(C:C;MATCH(99999999999;C:C)-1))). The First bit was ok but the next bit showed a blank if it was their week off. I want the blanks ignoring, and numbers only displayed. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
how i write a number on a column A and see that number on letters. | Excel Worksheet Functions | |||
Auto number w/ different letter-number combos in same column | Excel Worksheet Functions |