Find the first value in a row greater than zero

[Algeraist]

Hi,
I'm trying to find the column number of the first cell in a row that is greater than zero.

There are 31 columns of of data, so if statments aren't working for me.

any suggestions

Example
1 2 3 4 5 6 7 8 9
A 0 0 0 0 0 0 8 1 0 = 7
B 0 0 2 0 0 0 0 0 0 = 3
C 0 0 0 0 0 0 0 0 1 = 9

Thanks

[DonkeyOte]

You could use an Array

AF1: =MATCH(TRUE,$A1:$AE1>0,0)
confirmed with CTRL + SHIFT + ENTER

copy down as required

EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...

AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)

[twointum]

You could use an Array
AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)

This is glorious, I use it all the time, thank you!

[dreich]

Fantastic! That is very useful!

[patryk899]

You could use an Array

AF1: =MATCH(TRUE,$A1:$AE1>0,0)
confirmed with CTRL + SHIFT + ENTER

copy down as required

EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...

AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)

Thanks a lot for saving my ***!

[FortuneSyn]

AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)

Could someone explain to me why this works?

[sktneer]

Could someone explain to me why this works?

OK. Lets take a small example to show you how this works. Say the formula is MATCH(TRUE,INDEX($A1:$E1>0,0),0). And assuming that you have values 0 0 0 8 7 in the range A1:E1.

First of all the INDEX($A1:$E1>0,0) gets executed. It will return the array of True and False as you are using 0 as row number so instead of returning a single value it will return the whole array like this.....
{FALSE,FALSE,FALSE,TRUE,TRUE}

So your formula Match formula becomes
=MATCH(TRUE,{FALSE,FALSE,FALSE,TRUE,TRUE},0)

Now in the Match function, the lookup value is TRUE and lookup array is {FALSE,FALSE,FALSE,TRUE,TRUE} and 0 is for exact match. So the Match will return the position of first TRUE in the array {FALSE,FALSE,FALSE,TRUE,TRUE} which is 4.
Therefore the formula will return 4 since this is the position of first value which is greater than 0 in the range A1:A1.

I hope this helps.

[chirag050675]

Dear All
this is really useful & amazing
Regards,
Chirag

[SilverBullet17]

This is awesome - thanks all!

[PAP069]

Lets say then the values continue to be positive and then 0 again.
How i can find the position of this second occurence?

Example
1 2 3 4 5 6 7 8 9
A 0 0 0 0 0 0 8 1 0 = 9

[XOR LX]

@PAP069

Can you clarify with a few more examples, together with expected results?

Regards

[Pepe Le Mokko]

Administrative Note:

At PAP . since you have already created a new thread, there is no need to continue this one
https://www.excelforum.com/excel-for...ive-value.html

[Ceasefire3]

SKTNeer

I literally made an account just to thank you for your explanation. I've been spending the last 2 hours banging my head against a wall with some excel stuff and your explanation helped greatly.

Thank you so ******* much.

[sktneer]

SKTNeer

I literally made an account just to thank you for your explanation. I've been spending the last 2 hours banging my head against a wall with some excel stuff and your explanation helped greatly.

Thank you so ******* much.

Welcome to the forum!
Glad you found it helpful.