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?

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?

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?

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?