1-7 Columns, to find last column with figure in
 

Hi

I have prices in Mo, Tu, We, Th, Fr, Sa, Su Columns.

These figures arrive from information inserted in different sheets
called Mo, Tu, We, Th, Fr, Sa, Su.

What I would like to know is, what formula would be best to use to
check which column had a price in last. Say Mo R10, Tu R12, We R13 Tu,
Fr, Sa, Su, and it will give the answer R13 (which is Wednesday)

Can anyone recommend something?

Thanks
Sonar


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=400942

Can you add another row for some intermediate calculations?

If your prices are coming back in A2:G2, then add a formula in A3:
=count($A2:A2). Autofill that formula into B3:G3. In H3,
=index(A2:G2,match(G3,A3:G3,false))

The logic: for each day of the week, you'll have a cumulative count in row
3 of the number of days with prices. The match function will pick up which
day is the first where the cumulative count matches the total count for the
week. The index finds the price from that day.

"sonar" wrote:


Hi

I have prices in Mo, Tu, We, Th, Fr, Sa, Su Columns.

These figures arrive from information inserted in different sheets
called Mo, Tu, We, Th, Fr, Sa, Su.

What I would like to know is, what formula would be best to use to
check which column had a price in last. Say Mo R10, Tu R12, We R13 Tu,
Fr, Sa, Su, and it will give the answer R13 (which is Wednesday)

Can anyone recommend something?

Thanks
Sonar


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=400942

Im not exactly sure what you are asking. If cells A1 through G1
represent the days of the week (Mon - Sun), place this in cell H1 and
type a day number in cell I1.

=VALUE(INDIRECT(CHOOSE(I1,"A","B","C","D","E","F", "G")&"1"))

Matt


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=400942

Hi

If your headings are in A1:G1 and you have data in rows 2 onward, then one
way would be
in cell H2 enter
=ADDRESS(ROW(),COUNTA(B2:G2)+1)
This will give the cell address of the latest column to be populated.
Drag down column H to give the result for other rows.
The above works fine provided there are no blank cells in the row e.g. you
never have data in the Friday column, when the Thursday column is blank.

If there are likely to be blanks, then you would need to change the formula
to

=ADDRESS(2,MATCH(9.99999999999999E+307,A2:G2,1))
--
Regards

Roger Govier


"sonar" wrote in
message ...

Hi

I have prices in Mo, Tu, We, Th, Fr, Sa, Su Columns.

These figures arrive from information inserted in different sheets
called Mo, Tu, We, Th, Fr, Sa, Su.

What I would like to know is, what formula would be best to use to
check which column had a price in last. Say Mo R10, Tu R12, We R13 Tu,
Fr, Sa, Su, and it will give the answer R13 (which is Wednesday)

Can anyone recommend something?

Thanks
Sonar


--
sonar
------------------------------------------------------------------------
sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=400942