RE: Multiply IF AND OR comparisons
- From: ShaneDevenshire <ShaneDevenshire@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 16 Jun 2007 14:14:00 -0700
Hi,
Probably the best way to handle this is using the Solver Add-In. Choose
Tools, Add-Ins and check Solver. Then there will be a command on the Tools
menu called solver. This is a fairly sophisticated tool, to learn how to use
it Google "Excel Solver".
--
Cheers,
Shane Devenshire
"Hannu Laine" wrote:
Hi,.
the problem I'm wondering around is that how would be the wisest or most
efficient way to create the following comparison?
Here's the background for the question:
- Rounds are 7 weeks long.
- First round should be filled manually.
- The next shift round should be generated by using data from the last /
previous round.
- e.g. there is 14 workers (or more) and shifts would go like this, per
employee:
- starts 8, limit 4 shifts in one round
- starts 9, limit 2 shifts in one round
- starts 10, limit 1 shift in one round
- This would mean following with 14 employess:
- 9 employees in 8 shift
- 3 employees in 9 shift
- 2 employees in 10 shift
- Every week in round should be filled automatically (or drawn-filled) by
following conditions:
- any of shift limits (9, 3, 2) cannot exceed counting all employees,
limits should be filled
- if employee has exceeded one or more of the limits in previous round
he/she would be forced to be in the shift where he/she has been least.
e.g. employee #1 has been 5 times in 8 shift and 0 times in 9 shift and 1
time in 10 shift, he would be forced in to 9 shift as for first week,
next weeks in round would be generated after this.
I have managed to do *something*, I get partially generated shifts for like
5-7 employee and for 4-6 weeks, after that the whole thing starts to fall
into only one shift possibility and I just can't find out how this could be
done.
Here's a one round layout for example and by somekind of way from this
should be created the second round.
Employee down / Week right 1 2 3 4 5 6 7
Employee 1 10 8 8 8 8 8 9
Employee 2 10 8 8 8 8 8 9
Employee 3 9 10 8 8 8 8 9
Employee 4 9 10 8 8 8 10 8
Employee 5 9 9 10 8 8 8 8
Employee 6 8 9 9 8 8 8 9
Employee 7 8 9 9 10 8 8 8
Employee 8 8 8 9 10 8 8 8
Employee 9 10 8 9 9 10 10 8
Employee 10 8 8 8 9 10 8 8
Employee 11 8 8 8 9 9 10 8
Employee 12 8 8 8 8 9 10 8
Employee 13 8 8 8 8 9 9 10
Employee 14 8 8 8 8 8 9 10
Employees in shift / week 1 2 3 4 5 6 7
8 8 9 9 9 9 8 8
9 3 3 4 3 3 2 4
10 3 2 1 2 2 4 2
Round 1 in 8 shift in 9 shift in 10 shift Mostly Least Proposition
Employee 1 5 1 1 8 9 9
Employee 2 5 1 1 8 9 9
Employee 3 4 2 1 8 10 8
Employee 4 4 1 2 8 9 8
Employee 5 4 2 1 8 10 8
Employee 6 4 3 0 8 10 8
Employee 7 4 2 1 8 10 8
Employee 8 5 1 1 8 9 9
Employee 9 2 2 3 10 8 8
Employee 10 5 1 1 8 9 9
Employee 11 4 2 1 8 10 8
Employee 12 5 1 1 8 9 9
Employee 13 4 2 1 8 10 8
Employee 14 5 1 1 8 9 9
Shifts / employee Max Min Planned per Shift
8 5 2 4 9
9 3 1 2 3
10 3 0 1 2
- References:
- Multiply IF AND OR comparisons
- From: Hannu Laine
- Multiply IF AND OR comparisons
- Prev by Date: Re: Working with pairs of cells
- Next by Date: Re: @average function
- Previous by thread: Multiply IF AND OR comparisons
- Next by thread: Multi-conditional count
- Index(es):
Relevant Pages
|
