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.
Table of contents
YouTube Find Where First Non Zero Value is
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.
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.
Now it would be simple to create an INDEX that turns the number 3 into a year
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.
As shown below, the formula in B2 would be
=MATCH(TRUE,INDEX(C2:Z2<>0,0),0)
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.
Related
Hide columns based on cell value (simple macro)