Hi guys,

I need to return the second to last value (providing it is greater than
zero) from a data range. I already have a formula to return the value in the
last cell, this being:

=IFERROR(LOOKUP(2,1/($D7:$X70),$D7:$X7),"-")

I may be missing an obvious way to manipulate the above formula but would
appreciate any help.

Many thanks in advance,

Kind regards,

Ant

Try

=INDEX(7:7,LARGE(IF((D7:X70)*(D7:X70),COLUMN(D7: X7)),2))

Which is an array so commit with Ctrl+Shift+Enter NOT just enter

Mike

"Anto111" wrote:

Hi guys,

I need to return the second to last value (providing it is greater than
zero) from a data range. I already have a formula to return the value in the
last cell, this being:

=IFERROR(LOOKUP(2,1/($D7:$X70),$D7:$X7),"-")

I may be missing an obvious way to manipulate the above formula but would
appreciate any help.

Many thanks in advance,

Kind regards,

Ant

Why the repeat:

(D7:X70)*(D7:X70)

I've seen you do this before and thought it was just a typo. I've been known
to accidentally repeat repeat stuff!


--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Try

=INDEX(7:7,LARGE(IF((D7:X70)*(D7:X70),COLUMN(D7: X7)),2))

Which is an array so commit with Ctrl+Shift+Enter NOT just enter

Mike

"Anto111" wrote:

Hi guys,

I need to return the second to last value (providing it is greater than
zero) from a data range. I already have a formula to return the value in
the
last cell, this being:

=IFERROR(LOOKUP(2,1/($D7:$X70),$D7:$X7),"-")

I may be missing an obvious way to manipulate the above formula but would
appreciate any help.

Many thanks in advance,

Kind regards,

Ant

Biff,

Thanks for the correction. Having done it that way once; and the fact it
works, I simply never noticed the error, and because this is a common
question i've posted the same over elaborate answer several times. Now you
point it out of course it's blindingly obvious.


Mike


"T. Valko" wrote:

Why the repeat:

(D7:X70)*(D7:X70)

I've seen you do this before and thought it was just a typo. I've been known
to accidentally repeat repeat stuff!


--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Try

=INDEX(7:7,LARGE(IF((D7:X70)*(D7:X70),COLUMN(D7: X7)),2))

Which is an array so commit with Ctrl+Shift+Enter NOT just enter

Mike

"Anto111" wrote:

Hi guys,

I need to return the second to last value (providing it is greater than
zero) from a data range. I already have a formula to return the value in
the
last cell, this being:

=IFERROR(LOOKUP(2,1/($D7:$X70),$D7:$X7),"-")

I may be missing an obvious way to manipulate the above formula but would
appreciate any help.

Many thanks in advance,

Kind regards,

Ant

It looks like it is his method of doing either this...

--(D7:X70)

or this...

1*(D7:X70)

namely, forcing a logical expression to return a number.

Rick


"T. Valko" wrote in message
...
Why the repeat:

(D7:X70)*(D7:X70)

I've seen you do this before and thought it was just a typo. I've been
known to accidentally repeat repeat stuff!


--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Try

=INDEX(7:7,LARGE(IF((D7:X70)*(D7:X70),COLUMN(D7: X7)),2))

Which is an array so commit with Ctrl+Shift+Enter NOT just enter

Mike

"Anto111" wrote:

Hi guys,

I need to return the second to last value (providing it is greater than
zero) from a data range. I already have a formula to return the value in
the
last cell, this being:

=IFERROR(LOOKUP(2,1/($D7:$X70),$D7:$X7),"-")

I may be missing an obvious way to manipulate the above formula but
would
appreciate any help.

Many thanks in advance,

Kind regards,

Ant