| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
need to increment value
I have two columns ("A", "B"). Col "A", in the first row will contain "cyc".
then the remaining rows I want to increment, based on col "B". Col "B" will contain "Sun", "Mon", etc., for all days of the week. When the valaue in col "B" is not "Sat" and not "Sun", I want to increment the value from the preceding [weekday] row by one. Like this: Col A Col B Col C ----- ----- -------- cyc dow date --- --- -------- Sun 01/01/06 1 Mon 01/02/06 2 Tue 01/03/06 3 Wed 01/04/06 4 Thu 01/05/06 5 Fri 01/06/06 Sat 01/07/06 Sun 01/08/06 6 Mon 01/09/06 7 Tue 01/10/06 . . . . . . (Note that col "B" is dependant on col "C", which contains dates, so col "B" won't always start with "Sun". The value in col "C" will always start with the first day of the month, which can be any day of the week, and increment upward, to the end of the month.) In col "A" I've tried: if(a6="cyc",1,if((text(weekday(b11),"ddd")<"Sat") ,if((text(weekday(b11),"ddd")<"Sun"),=(A7)+1,),)) but it isn't giving me a number. (And then I still have the problem of the skipped weekend days.) Is there a limit to how much I can nest the "if"s? Any thoughts/ideas? Thanks in advance, Tom |
|
#2
|
|||
|
|||
|
Assuming that A1:C11 contains your table, and that the first row
contains your labels... D1: enter a 0 (zero) D2, copied down: =IF((B2<"")*(ISNA(MATCH(B2,{"Sat","Sun"},0))),LOO KUP(9.99999999999999E+3 07,$D$1:D1)+1,"") Hope this helps! In article , Tom wrote: I have two columns ("A", "B"). Col "A", in the first row will contain "cyc". then the remaining rows I want to increment, based on col "B". Col "B" will contain "Sun", "Mon", etc., for all days of the week. When the valaue in col "B" is not "Sat" and not "Sun", I want to increment the value from the preceding [weekday] row by one. Like this: Col A Col B Col C ----- ----- -------- cyc dow date --- --- -------- Sun 01/01/06 1 Mon 01/02/06 2 Tue 01/03/06 3 Wed 01/04/06 4 Thu 01/05/06 5 Fri 01/06/06 Sat 01/07/06 Sun 01/08/06 6 Mon 01/09/06 7 Tue 01/10/06 . . . . . . (Note that col "B" is dependant on col "C", which contains dates, so col "B" won't always start with "Sun". The value in col "C" will always start with the first day of the month, which can be any day of the week, and increment upward, to the end of the month.) In col "A" I've tried: if(a6="cyc",1,if((text(weekday(b11),"ddd")<"Sat") ,if((text(weekday(b11),"ddd" )<"Sun"),=(A7)+1,),)) but it isn't giving me a number. (And then I still have the problem of the skipped weekend days.) Is there a limit to how much I can nest the "if"s? Any thoughts/ideas? Thanks in advance, Tom |
|
#3
|
|||
|
|||
|
Sorry, I guess that should be A1 and A2, not D1 and D2....
In article , Domenic wrote: Assuming that A1:C11 contains your table, and that the first row contains your labels... D1: enter a 0 (zero) D2, copied down: =IF((B2<"")*(ISNA(MATCH(B2,{"Sat","Sun"},0))),LOO KUP(9.99999999999999E+3 07,$D$1:D1)+1,"") Hope this helps! |
|
#4
|
|||
|
|||
|
(Pls read all the way thru before responding.)
Well... There's the *good* news, and then there's the *bad* news... The Good news: This works. Thanks. Even more good news is that it'll still work if I enter "HOL" (holiday) into a col A cell, for those days we don't work. The Bad news: 1.) Where I want/need "cyc", I must enter "0" (zero). (In cell "A1".) Before I changed "cyc" to "0", the values being displayed were all the same "#N/A". Although...they *did* skip "Sat" & "Sun". I fixed this by inserting a new row, entering "0" then "hiding" the new row. It works. May not be elegant, but it works. (Or perhaps it *is* elegant.) 2.) Since I fixed #1, I went ahead and copied your formula to the other 5 months on the sheet. (Jul-Dec are on another sheet.) The formula works for the entire month of January and April. Apparently because they start with either Sat or Sun. The other 4 months don't work correctly. Here's what I'm getting: Feb: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue Mar: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue May: 1 Mon 1 Tue 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon Jun: 1 Thu 1 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue ### Wed ### Thu (### is displayed because the column is 5 wide. What is showing in the cell is "#VALUE!".) 3.) Notice the duplicating of the number "1" in May & June. I don't know why. I copy/pasted the forumla from the original January cell to all the other month's cell 1. Then I copied down. *I'm* confused... :) ----- *** NEVER MIND *** I got it. It's that absolute cell reference. Every month needs its own specific cell. And I hadn't changed any of them... Duh... Once I fixed that, all months now work correctly. ----- ALSO: I've been trying to decipher your repsonse, so at the least I can understand it and learn from it. I've printed out the help for LOOKUP, MATCH and IS functions. But I'm not really having as much luck as I'd like in understanding it. I mean, I understand it *generally*, but I'm trying to understand the individual pieces. ----- Why ("B3<"") times ISNA? ISNA = true if B3 < "Sat" and < "Sun", so go increment the value? MATCH is looking for "Sat" or "Sun" in cell B3, returning true if found? LOOKUP is looking for the maximum that a number can be (9.99999999999999E+307) within the range ($A$2:A2) and the highest number it finds is incremented by 1? ----- I don't suppose I impose one more time for some help in understanding what's happening, could I? Thanks so much, Tom On Thu, 23 Jun 2005 12:15:45 -0400, Domenic wrote: Sorry, I guess that should be A1 and A2, not D1 and D2.... In article , Domenic wrote: Assuming that A1:C11 contains your table, and that the first row contains your labels... D1: enter a 0 (zero) D2, copied down: =IF((B2<"")*(ISNA(MATCH(B2,{"Sat","Sun"},0))),LOO KUP(9.99999999999999E+3 07,$D$1:D1)+1,"") Hope this helps! |
|
#5
|
|||
|
|||
|
The formula should work for any month, regardless of the first day of
the month. So if A1 contains your label "Cyc" and A2 contains 0 (or vice versa, it doesn't matter), and your data starts in Row 3, try the following formula... A3, copied down: =IF((B3<"")*(ISNA(MATCH(B3,{"Sat","Sun"},0))),LOO KUP(9.99999999999999E+3 07,A$1:A2)+1,"") Note that I've change the reference for A1 so that the column reference is relative. This will allow you to copy the formula to other columns. Also, if you want "Hol" returned when a date falls on a holiday, try the following formula instead... A3, copied down: =IF(B3<"",IF(ISNUMBER(MATCH(C3,$E$3:$E$5,0)),"Hol ",IF(ISNA(MATCH(B3,{"Sa t","Sun"},0)),LOOKUP(9.99999999999999E+307,A$1:A2) +1,"")),"") ....where E3:E5 contains your list of holidays. You can expand this list. If you do, don't forget to change its reference accordingly. This formula uses the date in Column C for its evaluation and assumes that the dates are true date values. Does this help? In article , Tom wrote: (Pls read all the way thru before responding.) Well... There's the *good* news, and then there's the *bad* news... The Good news: This works. Thanks. Even more good news is that it'll still work if I enter "HOL" (holiday) into a col A cell, for those days we don't work. The Bad news: 1.) Where I want/need "cyc", I must enter "0" (zero). (In cell "A1".) Before I changed "cyc" to "0", the values being displayed were all the same "#N/A". Although...they *did* skip "Sat" & "Sun". I fixed this by inserting a new row, entering "0" then "hiding" the new row. It works. May not be elegant, but it works. (Or perhaps it *is* elegant.) 2.) Since I fixed #1, I went ahead and copied your formula to the other 5 months on the sheet. (Jul-Dec are on another sheet.) The formula works for the entire month of January and April. Apparently because they start with either Sat or Sun. The other 4 months don't work correctly. Here's what I'm getting: Feb: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue Mar: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue May: 1 Mon 1 Tue 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon Jun: 1 Thu 1 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue ### Wed ### Thu (### is displayed because the column is 5 wide. What is showing in the cell is "#VALUE!".) 3.) Notice the duplicating of the number "1" in May & June. I don't know why. I copy/pasted the forumla from the original January cell to all the other month's cell 1. Then I copied down. *I'm* confused... :) ----- *** NEVER MIND *** I got it. It's that absolute cell reference. Every month needs its own specific cell. And I hadn't changed any of them... Duh... Once I fixed that, all months now work correctly. ----- ALSO: I've been trying to decipher your repsonse, so at the least I can understand it and learn from it. I've printed out the help for LOOKUP, MATCH and IS functions. But I'm not really having as much luck as I'd like in understanding it. I mean, I understand it *generally*, but I'm trying to understand the individual pieces. ----- Why ("B3<"") times ISNA? ISNA = true if B3 < "Sat" and < "Sun", so go increment the value? MATCH is looking for "Sat" or "Sun" in cell B3, returning true if found? LOOKUP is looking for the maximum that a number can be (9.99999999999999E+307) within the range ($A$2:A2) and the highest number it finds is incremented by 1? ----- I don't suppose I impose one more time for some help in understanding what's happening, could I? Thanks so much, Tom |
|
#6
|
|||
|
|||
|
Yes, thanks.
On Thu, 23 Jun 2005 17:02:48 -0400, Domenic wrote: The formula should work for any month, regardless of the first day of the month. So if A1 contains your label "Cyc" and A2 contains 0 (or vice versa, it doesn't matter), and your data starts in Row 3, try the following formula... A3, copied down: =IF((B3<"")*(ISNA(MATCH(B3,{"Sat","Sun"},0))),LO OKUP(9.99999999999999E+3 07,A$1:A2)+1,"") Note that I've change the reference for A1 so that the column reference is relative. This will allow you to copy the formula to other columns. Also, if you want "Hol" returned when a date falls on a holiday, try the following formula instead... A3, copied down: =IF(B3<"",IF(ISNUMBER(MATCH(C3,$E$3:$E$5,0)),"Ho l",IF(ISNA(MATCH(B3,{"Sa t","Sun"},0)),LOOKUP(9.99999999999999E+307,A$1:A2 )+1,"")),"") ...where E3:E5 contains your list of holidays. You can expand this list. If you do, don't forget to change its reference accordingly. This formula uses the date in Column C for its evaluation and assumes that the dates are true date values. Does this help? In article , Tom wrote: (Pls read all the way thru before responding.) Well... There's the *good* news, and then there's the *bad* news... The Good news: This works. Thanks. Even more good news is that it'll still work if I enter "HOL" (holiday) into a col A cell, for those days we don't work. The Bad news: 1.) Where I want/need "cyc", I must enter "0" (zero). (In cell "A1".) Before I changed "cyc" to "0", the values being displayed were all the same "#N/A". Although...they *did* skip "Sat" & "Sun". I fixed this by inserting a new row, entering "0" then "hiding" the new row. It works. May not be elegant, but it works. (Or perhaps it *is* elegant.) 2.) Since I fixed #1, I went ahead and copied your formula to the other 5 months on the sheet. (Jul-Dec are on another sheet.) The formula works for the entire month of January and April. Apparently because they start with either Sat or Sun. The other 4 months don't work correctly. Here's what I'm getting: Feb: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue Mar: 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue May: 1 Mon 1 Tue 1 Wed 2 Thu 3 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon Jun: 1 Thu 1 Fri Sat Sun ### Mon ### Tue ### Wed ### Thu ### Fri Sat Sun ### Mon ### Tue ### Wed ### Thu (### is displayed because the column is 5 wide. What is showing in the cell is "#VALUE!".) 3.) Notice the duplicating of the number "1" in May & June. I don't know why. I copy/pasted the forumla from the original January cell to all the other month's cell 1. Then I copied down. *I'm* confused... :) ----- *** NEVER MIND *** I got it. It's that absolute cell reference. Every month needs its own specific cell. And I hadn't changed any of them... Duh... Once I fixed that, all months now work correctly. ----- ALSO: I've been trying to decipher your repsonse, so at the least I can understand it and learn from it. I've printed out the help for LOOKUP, MATCH and IS functions. But I'm not really having as much luck as I'd like in understanding it. I mean, I understand it *generally*, but I'm trying to understand the individual pieces. ----- Why ("B3<"") times ISNA? ISNA = true if B3 < "Sat" and < "Sun", so go increment the value? MATCH is looking for "Sat" or "Sun" in cell B3, returning true if found? LOOKUP is looking for the maximum that a number can be (9.99999999999999E+307) within the range ($A$2:A2) and the highest number it finds is incremented by 1? ----- I don't suppose I impose one more time for some help in understanding what's happening, could I? Thanks so much, Tom |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto increment over multiple worksheets | Excel Worksheet Functions | |||
| Relative reference autofill increment other than +1 | Excel Discussion (Misc queries) | |||
| How do I automatically increment worksheet number when pasting a . | Excel Worksheet Functions | |||
| Increment a counter in Excel | Excel Worksheet Functions | |||
| How do I increment a fill series by a number other than 1? Say I. | Excel Worksheet Functions | |||
Linear Mode
