Position of last POSITIVE value in a row

[SydneyGeek]

Hi, I'm trying to find the location of the last positive value in a row.
Say the data starts in T12 and goes 0,0,0,0,0,2,2,2,2,4,5,6,7,8,0,0,0...
There may be more than one sequence of positive numbers, but no negative numbers. I want to return the position of the LAST positive value in the row

Any ideas please?

Denis

 

[Aladin Akyurek]

I've seen formulas like this here before =MATCH(2,1/(T2:T20>0))
and I have tried to figure out how it works, but I don't get this one. Can someone explain how this works.

A) Lookup functions including MATCH() ignore error values.

B) 1 divided by a conditional (like in 1/{TRUE,FALSE,...}) will evaluate to an array of error values and numbers smaller than or equal to 1.

C) 2 is the smallest biggest number for arrays computed by (B).

D) Looking up (with MatchType set to 1/TRUE) a big number in an array or a range will return the last numerical value by binary search [ like in: =LOOKUP(9.99999999999999E+307,Range) ] from that array or range.

Hence, from A to D...

MATCH(2,1/SimpleOrCompositeConditional)


LOOKUP(2,1/SimpleOrCompositeConditional,< Array|Range >)

 

[Scott Huish]

Cool, thanks.

 

[SydneyGeek]

Aladin and Fairwinds,

thanks again -- not only for the answer but also for the explanations!

Denis