Find first value <>0 in subset

    Re: Find first value &lt;&gt;0 in subset


    Try this...


    1) First price from the bottom where Volume <>0,

    Code
    =INDEX(C5:C14,MAX(INDEX((B5:B14<>0)*(ROW(A5:A14)-ROW(A5)+1),)))


    2) First price from the bottom where Volumn <>0 and Name is Ben.

    Code
    =INDEX(C5:C14,MAX(INDEX((B5:B14<>0)*(A5:A14="Ben")*(ROW(A5:A14)-ROW(A5)+1),)))

    Regards.
    sktneer

    Re: Find first value &lt;&gt;0 in subset


    Thanks guys. Amazing how many different ways to get the same result? I couldn't add more stars to your reputation sktneer, as I've already awarded you stars for your earlier efforts, but thanks again, you're very helpful. :yourock:

    Re: Find first value &lt;&gt;0 in subset


    FWIW you can also use LOOKUP


    =LOOKUP(2,1/($A$5:$A$14="Ben")/($B$5:$B$14<>0),$C$5:$C$14)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login