in column A first value is 0, second is 0, third is 17, fourth is 0 and fifth is 32

calculating first non zero value in a column

in this case , first non zero value is 17. how to calculate it by formula

2

2 Answers

In cell B1:

=INDEX(A1:A5,MATCH(TRUE,INDEX(A1:A5<>0,),0))
2

Explanation for the above formula:

The inner Index function evaluates the values in the A1:A5 range to either TRUE or FALSE based on the specified condition, that is, not equal to 0. So 17 and 32 evaluate to TRUE, everything else to FALSE. The Match function returns the row number where the first instance of the specified condition TRUE is. This occurs on the 3rd row of the range, so Match evaluates to 3. Finally, the outer Index functions returns the value at row number 3 in the specified A1:A5 range, which is 17.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.