Formulas to find the first and last nonzero value in a range

[MikeWeaver]

I have a range similar to the table below, I would like to find using a formula the first and last nonzero value in the range. I would assume this should be done in two formulas, one to find the first nonzero value, and one to find the last.

[TABLE="width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

In this example I would like the formulas to return 2 (position of first nonzero value) and 5 (position of last nonzero value).

Thank you for any help!

 

[Aladin Akyurek]

I have a range similar to the table below, I would like to find using a formula the first and last nonzero value in the range. I would assume this should be done in two formulas, one to find the first nonzero value, and one to find the last.

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]12
[/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

In this example I would like the formulas to return 2 (position of first nonzero value) and 5 (position of last nonzero value).

Thank you for any help!

1. Control+shift+enter, not just enter:

=MATCH(TRUE,ISNUMBER(1/A2:G2),0)

2. Control+shift+enter, not just enter:

=MATCH(9.99999999999999E+307,1/ISNUMBER(1/A2:G2))

 

[MikeWeaver]

1. Control+shift+enter, not just enter:

=MATCH(TRUE,ISNUMBER(1/A2:G2),0)

2. Control+shift+enter, not just enter:

=MATCH(9.99999999999999E+307,1/ISNUMBER(1/A2:G2))

Thanks Aladin, that works perfectly. Could you provide some clarification on how those formulas are working (specifically the 1/.. parts) I'm having a tough time wrapping my head around it.

 

[Aladin Akyurek]

Thanks Aladin, that works perfectly.

You are welcome.

Could you provide some clarification on how those formulas are working (specifically the 1/.. parts) I'm having a tough time wrapping my head around it.

Dividing 1 with a blank cell or a cell with 0 would deliver a #DIV/0!, not a number. Hence ISNUMBER ends up with FALSE, otherwise with TRUE.

1. Matching TRUE against the TRUE/FALSE evaluations yields the relative position of the first TRUE value MATCH runs up against.

2. 1/ISNUMBER(1/A2:G2) boils down to dividing 1 by TRUE and FALSE evaluations (see above). Since TRUE = 1 and FALSE = 0 in Excel, we get a bunch of 1's and #DIV/0! values. MATCH ignores errors if it can and delivers the relative position of the last 1.

Here some links on the usage of MATCH in the second formula:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

Hope this helps.

 

[MikeWeaver]

You are welcome.



Dividing 1 with a blank cell or a cell with 0 would deliver a #DIV/0!, not a number. Hence ISNUMBER ends up with FALSE, otherwise with TRUE.

1. Matching TRUE against the TRUE/FALSE evaluations yields the relative position of the first TRUE value MATCH runs up against.

2. 1/ISNUMBER(1/A2:G2) boils down to dividing 1 by TRUE and FALSE evaluations (see above). Since TRUE = 1 and FALSE = 0 in Excel, we get a bunch of 1's and #DIV/0! values. MATCH ignores errors if it can and delivers the relative position of the last 1.

Here some links on the usage of MATCH in the second formula:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

Hope this helps.

Sorry I didn't respond to this sooner, but this was really informative and helped a lot. Keep doin you Aladin.

 

[Aladin Akyurek]

Sorry I didn't respond to this sooner, but this was really informative and helped a lot. Keep doin you Aladin.

Glad to help. Thanks for the nice feedback.

 

[MikeWeaver]

Glad to help. Thanks for the nice feedback.

I have another question if you're up for it, is there a way to do the same thing, but find the first and last value that are not within .01 (or -.01) of zero? This seems likes it would have to be a completely different formula. Thanks again!

 

[Aladin Akyurek]

I have another question if you're up for it, is there a way to do the same thing, but find the first and last value that are not within .01 (or -.01) of zero? This seems likes it would have to be a completely different formula. Thanks again!

Control+shift+enter:

1.

=MATCH(1,IF(ISNUMBER(1/A2:G2),IF(ABS(A2:G2)>0,1)),0)

2.

=MATCH(9.99999999999999E+307,IF(ISNUMBER(1/A2:G2),IF(ABS(A2:G2)>0.01,A2:G2)))