Formula to find first non-zero value in series?

  • Thread starter Thread starter Timd
  • Start date Start date
T

Timd

Hello. I have a row of cells containing non-zero integers, followed by
several cells containing zero. I would like a function that can be
placed at the end of the row, which will begin looking from the right-
hand side, and find the first non-zero cell, and display the value in
that cell.

For example:

216 6548 32151 215 21 518 0 0 0 0 0

The wanted formula would display 518. If the zero to the right of 518
were changed to a number, it would display that number.

I can write a long series of nested IF statements, but is there a
better way?

Thanks in advance!

Tim, Phoenix, AZ
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Try this:

=LOOKUP(1E100,1/A1:K1,A1:K1)
Click to expand...

Thanks Biff, worked great. You really are an Excel MVP!
 
Lovely, works fine, but how should the formula look like to find the value in
the opposite direction (from left to right)

expl:

0 0 0 4 6 8 9 0 0 formula returns: 4

Thanks in advance
 
One way (until Biff comes back):
=INDEX(A1:K1,MATCH(TRUE,A1:K1<>0,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Back
Top