2nd and 3rd last number in a column - how to get it?
 

Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt

2nd and 3rd last number in a column - how to get it?
 

In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)

In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0)


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala
wrote:

Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt

2nd and 3rd last number in a column - how to get it?
 

As long as there are no empty/blank/text cells within the range:

2nd to last:

=INDEX(V$3:V$1000,COUNT(V$3:V$1000)-1)

3rd to last:

=INDEX(V$3:V$1000,COUNT(V$3:V$1000)-2)

For the last number:

=LOOKUP(1E100,V$3:V$1000)


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates
the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show
the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt

2nd and 3rd last number in a column - how to get it?
 

Thanks Gord - works great!

Matt

"Gord Dibben" wrote:

In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)

In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0)


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala
wrote:

Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt

2nd and 3rd last number in a column - how to get it?
 

Thanks for the feedback.

See also Biff's shorter suggestion if you have no blanks.


Gord

On Mon, 11 Feb 2008 12:26:31 -0800, mpenkala
wrote:

Thanks Gord - works great!

Matt

"Gord Dibben" wrote:

In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)

In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0)


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala
wrote:

Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt