Find First Non Zero Value in a MS Excel list

If you build financial models, you may need to answer a question like when is the first time we go positive, or when was the first non zero value in a MS Excel list.

When is the first time we went positive

As shown below we may want to determine the first time we showed a profit after tax. This is easy to see but a bit more difficult to create on Excel.

First Non Zero Value in a MS Excel list

In order to do this we can play with the functionality of the MATCH and INDEX functions in Excel. In most cases we recommend that you do your calculations over as many cells as you need, but in this case the formula has to be in one cell.

The formula for the above situation would be

=MATCH(TRUE,INDEX(F24:J24>0,0),0)

To see why it works have a look below. The Index function creates an array of TRUE’s and FALSE’s. We have wrapped a MATCH function around it and asked for a TRUE. Similar to VLOOKUP, MATCH will find the first TRUE it sees.

First Non Zero Value in MS Excel list

Now it would be simple to create an INDEX that turns the number 3 into a year

First Non Zero Value in MS Excel list

First Non Zero Value in a MS Excel list

Using this same logic we can look for the First Non Zero Value in MS Excel list, or the first zero if you prefer. So below in cell B2 to B4, we want to see how long before there is activity (good or bad) in each product.

First Non Zero Value in MS Excel list

As shown below, the formula in B2 would be

=MATCH(TRUE,INDEX(C2:Z2<>0,0),0)

First Non Zero Value in MS Excel list

If you copy this down you will see that it specifies the first cell it sees that is not a zero.

If you wanted to show the first cell that IS a zero, you just need to change the <> to =  like this

=MATCH(TRUE,INDEX(C2:Z2=0,0),0)

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.