need to increment value

[Tom]

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

 

[Domenic]

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))),LOOKUP(9.99999999999999E+3
07,$D$11)+1,"")

Hope this helps!

 

[Domenic]

Sorry, I guess that should be A1 and A2, not D1 and D2....

 

[Tom]

(Pls read all the way thru before responding.)

Well... There's the *good* news, and then there's the *bad* news...

The Good
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
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

 

[Domenic]

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))),LOOKUP(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?

 

[Tom]

Yes, thanks.

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))),LOOKUP(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?