Formula

[Alan]

Hi,
I'm currently writing a service schedule for a fleet of vehicles. The
work is done Monday to Friday, ie over five days in a four week cycle and
there are many quite different types of vehicle.
If there are twenty-seven vehicles of identical type to service in a
five date period, then five would need to be done for three days and six on
the other two days. The actual day the work is done (Monday or Tuesday etc)
is immaterial.
Can anyone suggest a formula to return this split? I know it can be done
quite easily manually, but the goal posts move fairly often and it would be
nice to automate it. The purpose being to create as far as possible an even
work load every day.
Thanks in advance for any assistance,
Regards,
Alan.

Windows XP Pro
Office XP Pro

 

[JE McGimpsey]

One way:

Assume the number of vehicles in A1 and the number of days in A2. Assume
further that the number of cars on the first date is in D1, the number
on the second in D2, etc.

D1: =CEILING($A$1/$A$2,1)
D2: =IF(ROW()<=A$2,CEILING(($A$1-SUM(D$11))/($A$2-ROW(D1)),1),"")

Copy D2 down as far as necessary (i.e., the maximum number of days).

Note that this will always "front load" the work.

If you wish the extras to be spread out a bit more you could use:

D2: =IF(ROW()<=$A$2,ROUND(($A$1-SUM(D$11))/(A$2-ROW(D1)),0),"")

 

[Guest]

Alan

I'm assuming that 27 vehicle types is correct. I created an array with 4 columns. Three of them have fixed data: A, B, C. A represents the day of the week you want (B) Vehicle Type to be serviced. And C contains a formula for determining the day of the week and is as follows

=WEEKDAY($E$4,2) [Where $E$2 is =NOW() as shown at the top of column D as a header.

The formula in column C is applied to all Rows and returns a number representing the day of the week (1=Mon, etc).

Column D (below the current date) is also a formula applied to all Rows and is as follows:

=IF(AND($D$5=1,B5="MON"),(NOW()),IF(AND($D$5=2,B5="TUE"),(NOW()),IF(AND($D$5=3,B5="WED"),(NOW()),IF(AND($D$5=4,B5="THU"),(NOW()),IF(AND($D$5=5,B5="FRI"),(NOW()),"OFF SCHEDULE"))))

The results should look like the table below (Assuming today were Friday)

A1:A31 B1:B31 C1:C31 D13
(Day) (Type) Friday, May 28, 200
MON 1 5 OFF SCHEDUL
MON 2 5 OFF SCHEDUL
MON 3 5 OFF SCHEDUL
MON 4 5 OFF SCHEDUL
MON 5 5 OFF SCHEDUL
TUE 6 5 OFF SCHEDUL
TUE 7 5 OFF SCHEDUL
TUE 8 5 OFF SCHEDUL
TUE 9 5 OFF SCHEDUL
TUE 10 5 OFF SCHEDUL
WED 11 5 OFF SCHEDUL
WED 12 5 OFF SCHEDUL
WED 13 5 OFF SCHEDUL
WED 14 5 OFF SCHEDUL
WED 15 5 OFF SCHEDUL
THU 16 5 OFF SCHEDUL
THU 17 5 OFF SCHEDUL
THU 18 5 OFF SCHEDUL
THU 19 5 OFF SCHEDUL
THU 20 5 OFF SCHEDUL
THU 21 5 OFF SCHEDUL
FRI 22 5 Friday, May 28, 200
FRI 23 5 Friday, May 28, 200
FRI 24 5 Friday, May 28, 200
FRI 25 5 Friday, May 28, 200
FRI 26 5 Friday, May 28, 200
FRI 27 5 Friday, May 28, 200

Column C can be hidden since its only for determining the day of the week

 

[Alan]

Many Thanks,
Alan.

One way:

Assume the number of vehicles in A1 and the number of days in A2. Assume
further that the number of cars on the first date is in D1, the number
on the second in D2, etc.

D1: =CEILING($A$1/$A$2,1)
D2: =IF(ROW()<=A$2,CEILING(($A$1-SUM(D$11))/($A$2-ROW(D1)),1),"")

Copy D2 down as far as necessary (i.e., the maximum number of days).

Note that this will always "front load" the work.

If you wish the extras to be spread out a bit more you could use:

D2: =IF(ROW()<=$A$2,ROUND(($A$1-SUM(D$11))/(A$2-ROW(D1)),0),"")