Second to last entry

[Anto111]

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:$X7>0),$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

 

[Mike H]

Try

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

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

Mike

 

[T. Valko]

Why the repeat:

(D7:X7>0)*(D7:X7>0)

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

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

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

--(D7:X7>0)

or this...

1*(D7:X7>0)

namely, forcing a logical expression to return a number.

Rick