nested formula alternative

[Charlie]

I must be sleepy because I can't think of a better alternative to this. I'm
using nested IF's to get the last entry in a row, and moving the column
header (percent completed) to the end of the row. This seems clunky, plus
some sheets have 10% increments and the formula nesting is limited to 7. Any
ideas?

25% 50% 75% 100% Pct Complete
Alpha 10/12/07 25%
Bravo 10/12/07 10/14/07 50%
Charlie 10/13/07 10/19/07 75%
Delta 10/15/07 50%

This is the formula down column F.
=IF(E2<>"",$E$1,IF(D2<>"",$D$1,IF(C2<>"",$C$1,IF(B2<>"",$B$1,""))))

TIA
Charlie

 

[Bob Phillips]

=INDEX($A$1:$F$1,MAX(IF(B2:F2<>"",COLUMN(B2:F2))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Charlie]

I figured there was an array formula way of doing it but got stumped (said
the tree to the lumberjack.) Thanks.

 

[Bernard Liengme]

This also works =INDEX($B$1:$E$1,MATCH(MAX(B2:E2),B2:E2,0))
MATCH returns a number 1 to 4 giving location of the MAX value in the row of
dates
INDEX returns the corresponding header fro the top row
Note that if there are no dates in a row you will get N/A.
To get a blank returned when there is no date
=IF(COUNT(B2:E2),INDEX($B$1:$E$1,MATCH(MAX(B2:E2),B2:E2,0)),"")
best wishes

 

[Bernard Liengme]

Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes

 

[Nigel]

I tried this, but it assumes the values from left to right actually increase
in value.

I determine the max value in the range with the completion dates, then use
this value to match the value which yields the offset for the heading in row
1.

=OFFSET($B$1,0,MATCH(MAX(B2:E2),B2:E2,0)-1)

You can have a number of columns. One minor issue is that no dates returns
#N/A; but wrap the formula with a IF and ISERROR and set the True condition
to something like "Not Started"

 

[Bernard Liengme]

OOOPS, I misread Bob's reply. It is my formula that is not an array formula.
sorry!

 

[Charlie]

Yes, I figured you mis-posted when you said, "thanks for feedback."

Thanks to all for the ideas. I'm trying several of them.

Charlie

 

[Rick Rothstein \(MVP - VB\)]

This seems to work. Put the following formula in F2 and copy down...

=OFFSET(B2,-ROWS($1:1),COUNTA(B2:E2)-1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

I just read Nigel's posting and thought I should mention that, like his
formula, you can have a many columns as necessary (just adjust the B2:E2
range in the COUNTA function accordingly; and, unlike his formula, if no
dates exist in the range, the formula does not error out (the cell simply
remains blank).

Rick

 

[Bob Phillips]

You had me really confused there Bernard <vbg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Equiangular]

Hi,

May I know the use of -ROWS($1:1)?
It seems that this will always return -1

 

[Rick Rothstein \(MVP - VB\)]

May I know the use of -ROWS($1:1)?

It seems that this will always return -1

Not when you copy the formula it's in down. The second "1" (the one without
the $ sign) will increment as the formula is copied down.

Rick

 

[Equiangular]

I hv overlooked that
Thx a lot

Not when you copy the formula it's in down. The second "1" (the one
without the $ sign) will increment as the formula is copied down.

Rick