Row Formula
 

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?

Hi!

Here's one way.

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

=INDEX(A1:L1,MAX(IF(A1:L10,COLUMN(A1:L1))))

Biff

wrote in message
oups.com...
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?

Unfortunately the suggested formula does not work


wrote:
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?

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" wrote in message
oups.com...


Unfortunately the suggested formula does not work


wrote:
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?

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

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

"Tom" wrote in message
ups.com...
The error that I am getting is #value! and when checking the formula it
points to A1:L10 as to where the error is coming from