Efficient Formula?

[David Heaton]

Hi,
I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....


I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.



The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...


WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon
Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee name x x x x x x x x x x x x x x x x x x x x
x x x x x




below is the CurrentRoster sheet which ended up like this ( the first col is
the first day of whichever month we are in (1/11/07).

NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri
Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x
x x


I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))The NAME cells on both sheets are at A3.The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.ROSTERCOL was just a static reference to the column the roster started on,in this case 2I hope i have explained this ok.thanks in advanceDavid Heaton

 

[Brian Withun]

Hi,
I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....

I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.

The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...

WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon
Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee name x x x x x x x x x x x x x x x x x x x x
x x x x x

below is the CurrentRoster sheet which ended up like this ( the first col is
the first day of whichever month we are in (1/11/07).

NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri
Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x
x x

I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))The NAME cells on both sheets are at A3.The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.ROSTERCOL was just a static reference to the column the roster started on,in this case 2I hope i have explained this ok.thanks in advanceDavid Heaton

Here are my thoughts about your approach:

It seems your data is growing to the right as days go by, and your
data grows 'down' as new staff names are added. I suggest designing
sheets which grow faster downward than sideways. You have 65536 rows
to work with while only "IV" (or 265) columns. Grow in the direction
you have the most space. You'll run out of days after less than a
year if you grow to the right. That, and it's more natural to scroll
down than to scroll sideways.

Your staff names will then stretch across the top of your sheet, and
days will appear on the left, increasing as you move down.

Second, as I understand it, the staff work in a pattern that repeats
every 40 days? As a member of your staff, I'll work today if and only
if I worked 40 days ago (and today is not a holiday). OFFSET() is the
proper function to achieve this. Staff schedules will all have this
formula:

=OFFSET($A$1,ROW()-41,COLUMN())

....assuming that there is exactly one ROW per day. This function
pulls the cell value from the cell 40 rows above the current cell.

I would create a Function IsHoliday() which, given a date, returns
TRUE or FALSE. Then modify the staff schedule formula as below:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,COLUMN())),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

This formula will only produce valid results in or below row 41.
Above that you'll need to define the work pattern for each staff
member by hand.

As the days, weeks, and months go by this technique will create a lot
of cells with a lot of formulas. It will bog down eventually. To
keep it fast you should periodically COPY and PasteSpecial (paste
values only) on PAST dates. Like when you close down November be sure
to replace all the formulas in November with Values. Values won't
slow down Excel at all. Don't let your sheet fill with thousands of
formulas calculating historical dates...

Hope this helps a little.

Brian Herbert Withun

 

[Brian Withun]

Here are my thoughts about your approach:

It seems your data is growing to the right as days go by, and your
data grows 'down' as new staff names are added. I suggest designing
sheets which grow faster downward than sideways. You have 65536 rows
to work with while only "IV" (or 265) columns. Grow in the direction
you have the most space. You'll run out of days after less than a
year if you grow to the right. That, and it's more natural to scroll
down than to scroll sideways.

Your staff names will then stretch across the top of your sheet, and
days will appear on the left, increasing as you move down.

Second, as I understand it, the staff work in a pattern that repeats
every 40 days? As a member of your staff, I'll work today if and only
if I worked 40 days ago (and today is not a holiday). OFFSET() is the
proper function to achieve this. Staff schedules will all have this
formula:

=OFFSET($A$1,ROW()-41,COLUMN())

...assuming that there is exactly one ROW per day. This function
pulls the cell value from the cell 40 rows above the current cell.

I would create a Function IsHoliday() which, given a date, returns
TRUE or FALSE. Then modify the staff schedule formula as below:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,COLUMN())),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

This formula will only produce valid results in or below row 41.
Above that you'll need to define the work pattern for each staff
member by hand.

As the days, weeks, and months go by this technique will create a lot
of cells with a lot of formulas. It will bog down eventually. To
keep it fast you should periodically COPY and PasteSpecial (paste
values only) on PAST dates. Like when you close down November be sure
to replace all the formulas in November with Values. Values won't
slow down Excel at all. Don't let your sheet fill with thousands of
formulas calculating historical dates...

Hope this helps a little.

Brian Herbert Withun

Allow me to correct a formula above. As I put it in my last message
it will not work:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,COLUMN())),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

The problem here is that I am passing the actual schedule value to the
IsHoliday function (as you show it it is either an 'x', an 'h', or
blank)

What I (you) need to do is pass a >date< to IsHoliday(). The date
will be from column A with an index of 1:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,1)),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

That should be closer to the mark.

Best of luck,

Brian Herbert Withun

 

[Brian Withun]

Allow me to correct a formula above. As I put it in my last message
it will not work:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,COLUMN())),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

The problem here is that I am passing the actual schedule value to the
IsHoliday function (as you show it it is either an 'x', an 'h', or
blank)

What I (you) need to do is pass a >date< to IsHoliday(). The date
will be from column A with an index of 1:

=IF(IsHoliday(OFFSET($A$1,ROW()-41,1)),"H",OFFSET($A
$1,ROW()-41,COLUMN()))

That should be closer to the mark.

Best of luck,

Brian Herbert Withun

Furthermore, it occurs to me that 40 days after every holiday
(indicated by "H"), all staff members will be shown as on holiday
again. The crux is that, if 40 days ago I was on holiday, should I
work today or not?

Consider each employee as having 3 possible states:

"" (not scheduled)
"X" (scheduled)
"H" (scheduled, but off due to holiday)

if today is a holiday AND
....40 days ago was "" then I'm "" today
....40 days ago was "X" then I'm "H"
....40 days ago was "H" then I'm "H"

if today is NOT a holiday AND
....40 days ago was "" then I'm "" today
....40 days ago was "X" then I'm "X"
....40 days ago was "H" then I'm "X"

Brian Herbert Withun