formula to determine the first column containing any data
 

I have columns of data starting from Jan 2002 thru Sep 04 (33 columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine against each row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when I had a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using offset I can
determine the month.

sd,

=SUMPRODUCT(MAX((2:2<"")*(COLUMN(2:2))))

so,

=INDEX($1:$1,SUMPRODUCT(MAX((2:2<"")*(COLUMN(2:2) ))))

HTH,
Bernie
MS Excel MVP

"sd" wrote in message
...
I have columns of data starting from Jan 2002 thru Sep 04 (33 columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine against each
row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when I had a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using offset I
can
determine the month.

Hi
as alternative:
=LOOKUP(2,1/(1:1<""),1:1)

Also see http://www.xldynamic.com/source/xld.LastValue.html

--
Regards
Frank Kabel
Frankfurt, Germany

"sd" schrieb im Newsbeitrag
...
I have columns of data starting from Jan 2002 thru Sep 04 (33
columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine against
each row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when I
had a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using offset
I can
determine the month.

Cool formula, Frank. The OP would need it to be

=LOOKUP(2,1/(2:2<""),1:1)

Bernie
MS Excel MVP

"Frank Kabel" wrote in message
...
Hi
as alternative:
=LOOKUP(2,1/(1:1<""),1:1)

Also see http://www.xldynamic.com/source/xld.LastValue.html

--
Regards
Frank Kabel
Frankfurt, Germany

"sd" schrieb im Newsbeitrag
...
I have columns of data starting from Jan 2002 thru Sep 04 (33
columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine against
each row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when I
had a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using offset
I can
determine the month.

Hi Bernie
correct. Good spot.
Thanks for this formula though should go to Aladin (at least I think he
was the first one who posted this LOOKUP formula type)

Note: This formula is also quite slow. So if The OP only has numeric
values to look for he may also try:
=LOOKUP(9.999999E307,2:2,1:1)

--
Regards
Frank Kabel
Frankfurt, Germany

"Bernie Deitrick" <deitbe @ consumer dot org schrieb im Newsbeitrag
...
Cool formula, Frank. The OP would need it to be

=LOOKUP(2,1/(2:2<""),1:1)

Bernie
MS Excel MVP

"Frank Kabel" wrote in message
...
Hi
as alternative:
=LOOKUP(2,1/(1:1<""),1:1)

Also see http://www.xldynamic.com/source/xld.LastValue.html

--
Regards
Frank Kabel
Frankfurt, Germany

"sd" schrieb im Newsbeitrag
...
I have columns of data starting from Jan 2002 thru Sep 04 (33
columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine
against
each row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when
I
had a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using
offset
I can
determine the month.

=LOOKUP(9.99999999999999E+307,2:2,1:1)

where 2:2 houses the numeric data of interest and 1:1 true dates.

sd Wrote:
I have columns of data starting from Jan 2002 thru Sep 04 (33 columns)
Data contains numbers or are blank
Is there any formula (other than a VBA) that can determine against each
row
the last month I had any number
Example:-

Jan-00 feb-00 mar-00 apr-00 may-00
50 12

The result should be Feb-00 as that month is the last time when I had
a
data (and not blank) looking from right to left.
Just the formula to determine the column # will suffice, using offset I
can
determine the month.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=276594