Row Formula

  • Thread starter Thread starter tsanders123
  • Start date Start date
T

tsanders123

How do I create a formula that will pick the furthest right non balnk
value in a row. The colums represent the months of the year and every
month data is entered in the row. Sometimes the cell will be blank.

ie

J F M A M J J A S O N D Value

3 5 0 6 8 3 3


In the above case the value returned would be 3 if data was required
for August.

If a value was required for September the value returned would also be
3 because the cell representing September is blank.

Can anyone help as this is driving me up the wall.

Thanks
 
If you just want the furthest right, you can use

=INDEX(A2:I2,MAX((COLUMN(INDIRECT("A2:I2")))*(A2:I2<>"")))

which is an array formula, so commit with Ctrl-Shift-Enter.

If you want upto a particular month, put the month number in M1 and use

=INDEX(INDIRECT("A2:"&CHAR(64+M1)&"2"),MAX((COLUMN((INDIRECT("A2:"&CHAR(64+M
1)&"2")))*(INDIRECT("A2:"&CHAR(64+M1)&"2")<>""))))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote...
If you just want the furthest right, you can use

=INDEX(A2:I2,MAX((COLUMN(INDIRECT("A2:I2")))*(A2:I2<>"")))

which is an array formula, so commit with Ctrl-Shift-Enter.

If you want upto a particular month, put the month number in M1 and use
....
Click to expand...
....

An alternative general formula would be

=LOOKUP(2,1/(A2:L2<>""),A2:L2)

which doesn't require array entry. To restrict to only the first #
columns, where # is given in cell M1,

=LOOKUP(2,1/(OFFSET(A2:L2,0,0,1,M1)<>""),OFFSET(A2:L2,0,0,1,M1))

which also doesn't require array entry.

Finally, INDIRECT is seldom more efficient than OFFSET.

INDIRECT("A2:"&CHAR(64+M1)&"2") == OFFSET(A2,0,0,1,M1)
 
Harlan said:
Bob Phillips wrote...
...

An alternative general formula would be

=LOOKUP(2,1/(A2:L2<>""),A2:L2)

which doesn't require array entry. To restrict to only the first #
columns, where # is given in cell M1,

=LOOKUP(2,1/(OFFSET(A2:L2,0,0,1,M1)<>""),OFFSET(A2:L2,0,0,1,M1))

which also doesn't require array entry.

Finally, INDIRECT is seldom more efficient than OFFSET.

INDIRECT("A2:"&CHAR(64+M1)&"2") == OFFSET(A2,0,0,1,M1)
Click to expand...
 
Unfortunately this formula does not work, it is not returning the
correct number. A value of 0 is being returned.
 
Harlan,

I stand corrected the first formula works perfectly!! You are a guru.
Could you please explain the logic.

Thanks very much.

Tom
 
The A2:L2<>"" creates an array of TRUE/FALSE for each cell in that range
that has a value/no value.

Dividing 1 by this array produces a further array of 1 or #DIV/0.

As the largest value is 1, doing a LOOKUP of 2 into this array will find the
largest value (see help) in the array, that is the last 1 value, and use
that index to get the value from the result range, which is also A2:L2.

In the limited range example, the OFFSET is simply used to restrict the
range to the cells defined by the value in M1.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,

The formula works except if all the cells in the row are empty the
formula returns a value of #N/A and it should return a value of 0.
 
Easily resolved Tom

=IF(COUNTA(OFFSET(A2:L2,0,0,1,M1))=0,0,LOOKUP(2,1/(OFFSET(A2:L2,0,0,1,M1)<>"
"),OFFSET(A2:L2,0,0,1,M1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,

Do you have a solution for the first formula as that is the one I am
using.

Much appreciated.

Thanks
 
Hi Tom,

Harlan's first formula?

=LOOKUP(2,1/(A2:L2<>""),A2:L2)

=IF(COUNTA(A2:L2)=0,0,LOOKUP(2,1/(A2:L2<>""),A2:L2)


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob,

Yes that was the formula I was referring to. Your solution works
perfectly. Thanks for your help. I will now be able to move forward
with my analysis.

Thanks again
 
Back
Top