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

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

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)

--
Jacob (MVP - Excel)


"gergster" wrote:

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

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.


"gergster" wrote:

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

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:C Q3),),2)-COLUMN(B3:CQ3)+1)



"gergster" wrote in message
...
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