Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
Dynamic (?) formula | Excel Worksheet Functions | |||
Polynimial trandline formula | Charts and Charting in Excel | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |