Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative formula for getting totals | Excel Worksheet Functions | |||
What is an alternative to nested IF statements? | New Users to Excel | |||
Nested IF fn Alternative | Excel Discussion (Misc queries) | |||
too many arguments in formula. alternative? | Excel Discussion (Misc queries) | |||
Nested Vlookup or alternative? | Excel Worksheet Functions |