Row Formula

[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?

 

[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

 

[Tom]

Unfortunately the suggested formula does not work

 

[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

 

[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

 

[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