nested formula alternative
 

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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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

"Bob Phillips" wrote:

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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 V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email


"Charlie" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Charlie" wrote in message
...
I figured there was an array formula way of doing it but got stumped (said
the tree to the lumberjack.) Thanks. :-)

"Bob Phillips" wrote:

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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"

--

Regards,
Nigel




"Charlie" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

OOOPS, I misread Bob's reply. It is my formula that is not an array formula.
sorry!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Charlie" wrote in message
...
I figured there was an array formula way of doing it but got stumped (said
the tree to the lumberjack.) Thanks. :-)

"Bob Phillips" wrote:

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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

"Bernard Liengme" wrote:

OOOPS, I misread Bob's reply. It is my formula that is not an array formula.
sorry!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Charlie" wrote in message
...
I figured there was an array formula way of doing it but got stumped (said
the tree to the lumberjack.) Thanks. :-)

"Bob Phillips" wrote:

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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

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

Rick


"Charlie" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
This seems to work. Put the following formula in F2 and copy down...

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

Rick


"Charlie" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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)

"Bernard Liengme" wrote in message
...
OOOPS, I misread Bob's reply. It is my formula that is not an array
formula.
sorry!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Thanks for feedback.
Please note this is not really an array formula - no need to use
CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Charlie" wrote in message
...
I figured there was an array formula way of doing it but got stumped
(said
the tree to the lumberjack.) Thanks. :-)

"Bob Phillips" wrote:

=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" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

Hi,

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

Rick Rothstein (MVP - VB) wrote:
This seems to work. Put the following formula in F2 and copy down...

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

Rick


"Charlie" wrote in message
...
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(B 2<"",$B$1,""))))

TIA
Charlie

nested formula alternative
 

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

nested formula alternative
 

I hv overlooked that
Thx a lot

Rick Rothstein (MVP - VB) wrote:
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