Row Formula

T

tsanders123

How do I create a formula that returns the furthest to the right last
non zero number in a row with up to 15 columns

ie colums shown with values in cells A1 to L1


A B C D E F G H I J K L
1 4 6 0 8 4 0 7 5

How Do I create a formula to return the value of 5, in this case cell
I1?
 
B

Biff

Hi!

Here's one way.

Entered with the key combo of CTRL,SHIFT,ENTER:

=INDEX(A1:L1,MAX(IF(A1:L1>0,COLUMN(A1:L1))))

Biff
 
B

Biff

Hi!

What does: "does not work" mean?

Are you getting an error? An incorrect result?

Did you enter the formula as an array? After typing the formula, instead of
just hitting ENTER you MUST use the key combination of CTRL,SHIFT,ENTER.
When done properly Excel will place curly braces { } around the formula. You
must use the key combo to accomplish this. You can not just type them in.

Biff
 
T

Tom

The error that I am getting is #value! and when checking the formula it
points to A1:L1>0 as to where the error is coming from
 
B

Biff

Well, I can't figure out why you would get a #VALUE! error.

Even if the "numbers" you had in that range were actually TEXT the formula
would still work although the result could be incorrect.

I can send you a sample file that shows that this formula DOES work. Or,
perhaps you could send me your file so I can see what's really going on.
There has to be some detail about your data that you're not telling me.

Biff
 
Top