nested formula alternative

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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)
 
I figured there was an array formula way of doing it but got stumped (said
the tree to the lumberjack.) Thanks. :-)
 
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
 
Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes
 
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"
 
OOOPS, I misread Bob's reply. It is my formula that is not an array formula.
sorry!
 
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
 
This seems to work. Put the following formula in F2 and copy down...

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

Rick
 
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
 
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)
 
May I know the use of -ROWS($1:1)?
It seems that this will always return -1
Click to expand...

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
 
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
Click to expand...
 
Back
Top