Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Yr to Yr comparisons | Excel Discussion (Misc queries) | |||
add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
Spreadsheet Comparisons | Excel Discussion (Misc queries) | |||
Can I add more than 2 comparisons to an IF statement?? | Excel Discussion (Misc queries) | |||
string comparisons | Excel Discussion (Misc queries) |