How to find first occurance based on given condition

[Manamana]

I like to get the position of first non-zero number out of a data range like this:

0 0 0 0 5 4 0 6 0

I am after result of 5 through any formula. Please help!

 

[T. Valko]

what is the reason of using Control+shift+enter when i use array formula, what is the difference between normal formula & array formula.

See this...

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Aladin Akyurek]

Hi Aladin

what is the reason of using Control+shift+enter when i use array formula, what is the difference between normal formula & array formula.

Control+shıft+enter (a conventıon by desıgn) sıgnals Excel to treat the formula in question as an array formula, not as a regular type of formula. Excel's Help has also some ınfo on "array" formulas.


Posted from Güzelçamlı at the Aegean cost.

 

[ramsee]

Perhaps,

=MATCH(0, A1:J1) + 1

Hi Mike,

Your solution is excellent, i am just failing to understand how it works in a particular scenario. suppose the data is like this

0 0 0 8 0 9 9 9 9 9 9 , I thought the answer to the formula would be 5 , but to my surprise it is 4. I was thinking the formula would count all the 0's and add 1 to it , but my thinking was wrong. it just amazes me , perhaps you could calrify a bit. Many thanks .

-Venky

 

[energman58]

CSE tells excel its an array formula

An array formula evaluates all the cells in the array rather than doing what you would normally expect so you can have a formula which doesnt seem to make sense so the array formula sum(A1:A10*B1:B10) evaluates each A1*B1, A2*B2 and so on and then sums them to give the answer.

Some formulas will give different results depending on whether they are entered as array formulas or not.

 

[Maheshp]

Thanks T. Valko & Aladin for explanation.

 

[T. Valko]

Thanks T. Valko & Aladin for explanation.

You're welcome!