Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Multiply IF AND OR comparisons

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Multiply IF AND OR comparisons

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Yr to Yr comparisons cisse_5 Excel Discussion (Misc queries) 7 May 15th 09 06:10 PM
add value of 4 cells, multiply by 3 subtract 72 multiply by 80% George A. Yorks Excel Discussion (Misc queries) 10 October 25th 06 09:45 PM
Spreadsheet Comparisons dah Excel Discussion (Misc queries) 4 August 4th 06 12:28 AM
Can I add more than 2 comparisons to an IF statement?? joshua Excel Discussion (Misc queries) 3 July 27th 06 06:58 AM
string comparisons Ron Excel Discussion (Misc queries) 0 February 17th 05 06:02 PM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"