Nested Ifs - need a better way

[VRandy]

I have a table with 52 columns (date of week in a year)
with mm-dd-yy as column heads (C2:BB2)
C2 has the start date of the last week in December
BB2 has the date Jan 1)
The rows contain integers. and may be empty.

For each row I want the earliest date that has a value > 0 and that
date should be shown in column B for each row.

for example:
B2 C2 D2 E2 F2 BB2
Row2 12-28-73 12-21-73 12-14-73 12-7-73 ...1-1-73
Row3 44 15 ...
Row4 5 12 8 ... 9
Row5 4 7

Results should return:
Row B3 = 12-7-73
Row B4 = 1-1-73
Row B5 = 12-21-73

I need to search from BB2 backwards to C2 for the first cell that has
a value > ""

If I only had 7 weeks then a nested IF would work:
=If(LEN(BB3)>0,BB2,=IF(LEN(BA3)>0,BA2,=IF(LEN(AZ3)>0,AZ2,...))))
But I would need 52 nested IF's for this.

I know I could use VB to get the result using:
Active Cell.End.xlLeft
but is there a way to do it without resorting to VB?
Thanks for any help.
VRandy

 

[jpendegraft]

The MIN function will return the earliest date within a range o
dates....I could not interpert whether this was applicable in you
situation.

to retreive the earliest date between B2 and B12 the formula would loo
like: =MIN(B2:B12

 

[VRandy]

The MIN function will return the earliest date within a range of
dates....I could not interpert whether this was applicable in your
situation.

to retreive the earliest date between B2 and B12 the formula would look
like: =MIN(B2:B12)

Thank you for your reply but the dates (as date format) do not really
matter. I want a formula to put in each row of column B that will show
the value of row 2 in the right most column that has a value > 0.

VRandy

 

[Frank Kabel]

Hi
one way:
=INDEX($A$2:$BB$2,1,MAX(IF(A3:BB2<>"",ROW(A3:BB3),0)))
enter this as array formula (CTRL+SHIFT+ENTER) and copy down for the
other rows
Note: This checks for the last non-empty cell (regardless if its a text
or number). If you want to get the last number try
=INDEX($A$2:$BB$2,1,MAX(IF(ISNUMBER(A3:BB2),ROW(A3:BB3),0)))

HTH
Frank

 

[VRandy]

Thank you Frank! You put me on the right track.
The solution that works for me:
=INDEX($A$2:$BB$2,1,MAX(IF(C3:BB3<>"",COLUMN(C3:BB3),0)))
Note the replacement of ROW in your suggestion with COLUMN in the
working solution. The IF statement also excludes cols A and B which
was creating a circular reference problem.
Thank you again!
VRandy