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

  • Thread starter Thread starter gergster
  • Start date Start date
G

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
 
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)
 
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.
 
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)
 
Back
Top