Prev: copy formula
Next: search a row for a word and if found
From: gergster on 16 Apr 2010 02:32
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
From: Jacob Skaria on 16 Apr 2010 03:19
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
From: Mike H on 16 Apr 2010 05:03
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
From: Steve Dunn on 16 Apr 2010 08:58
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)



"gergster" <gergster(a)discussions.microsoft.com> wrote in message
news:A4C90F3B-DB01-4E0A-A053-566F543E01AF(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: copy formula
Next: search a row for a word and if found