Finding NEXT to last, non zero numeric value in a row

[gergster]

trying to find a way to automatically return (via formula, not macro- I don't
know squat about macros), automatically return the NEXT TO LAST, non zero,
numeric value in a row of cells. All info in the range is numeric. I found
this one to find the LAST value:
=LOOKUP(E1+100,1/(B3:CQ3),B3:CQ3)

but i need the value to the immediate LEFT of what that formula returns. I
am a complete newbie. Speak slowly and use small words. Don't know anything
about macros, or any formulas beyond simple math

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=INDEX(B3:CQ3,LARGE(IF(ISNUMBER(B3:CQ3),IF(B3:CQ3<>0,
COLUMN(B3:CQ3))),2)-COLUMN(B3:CQ3)+1)

 

[Mike H]

Hi,

Try this array formula

=INDEX(3:3,LARGE(IF((B3:AQ3<>"")*ISNUMBER(B3:AQ3)*(B3:AQ3<>""),COLUMN(B3:AQ3)),2))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Steve Dunn]

Hi,

small variation from the others. You said all info in the range is numeric,
so ISNUMBER is not required, and this can be entered normally (not as an
array formula):

=INDEX(B3:CQ3,LARGE(INDEX((B3:CQ3<>"")*COLUMN(B3:CQ3),),2)-COLUMN(B3:CQ3)+1)