Rotating Schedule based on Unit Volume Share

artikyulashun

New Member
Joined
Aug 21, 2012
Messages
41
ABCWeek 1Week 2
MFG AMFG BMFG CMFG AMFG BMFG CMFG AMFG BMFG C
Bananas.6.2.2ON
Apples.1.4.5ON
Oranges.3.5.2ON

<tbody>
</tbody>

Columns A,B and C represent Percentage of Unit Volume by MFG for the Fruits to the left.

Using these numbers, I would like to generate a rotating 13-week promotional schedule for each item. Maximum number of weeks in a row that an MFG can have any one specific item on sale is two weeks. However, at the end of the of the 13-weeks, I'd like the number of instances that, for example, MFG A has Bananas on sale equal or near their share or .6.

My initial thought was you could use a ratio to create a patterned binary list of 1 and 0 or "on" and "off" for each item, but perhaps it is easier with these percentages? Perhaps this is simply a manual task.

Your feedback is appreciated.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,573
Intriguing problem. Just to be sure I understand, every week every product will be on sale for one of the manufacturers? If so, I reorganized it a bit so each week has just 1 column, and instead of "ON", it shows the name of the manufacturer instead.

Given that, I started with this:

ABCDEFGHIJKLMNOPQR
1MFG AMFG BMFG CWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13
2Bananas0.60.20.2MFG AMFG CMFG AMFG CMFG CMFG AMFG AMFG AMFG CMFG AMFG CMFG CMFG A
3Apples0.10.40.5MFG AMFG CMFG CMFG BMFG CMFG CMFG CMFG BMFG BMFG AMFG BMFG CMFG C
4Oranges0.30.50.2MFG CMFG BMFG BMFG BMFG AMFG BMFG AMFG BMFG BMFG BMFG CMFG CMFG B

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=INDEX($B$1:$D$1,MATCH(RAND(),SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3}))))

<tbody>
</tbody>

<tbody>
</tbody>


Put the formula in F2, then drag it to the rest of the cells in the table.

The formula in F2 will randomly assign a manufacturer to each cell, based on the ratios in columns A:C. So each manufacturer should be listed roughly the right percentage, but it could vary some. The problem with this is that it does not take into account the maximum 2-week limit. That proved to be a tougher nut to crack. But I came up with this:

ABCDEFGHIJKLMNOPQR
1MFG AMFG BMFG CWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13
2Bananas0.60.20.2MFG AMFG AMFG BMFG AMFG CMFG AMFG BMFG AMFG AMFG BMFG BMFG AMFG A
3Apples0.10.40.5MFG BMFG CMFG CMFG BMFG CMFG CMFG BMFG BMFG CMFG BMFG BMFG CMFG C
4Oranges0.30.50.2MFG AMFG BMFG BMFG AMFG CMFG BMFG AMFG CMFG BMFG BMFG AMFG BMFG A

<tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
F2{=INDEX($B$1:$D$1,LARGE(LOOKUP(ROW(INDIRECT("1:10"))-1,SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3}))*10,{1,2,3}*SIGN(((D2<>E2)+(E2<>$B$1:$D$1)))),RANDBETWEEN(1,SUM(10*($B2:$D2*SIGN(((D2<>E2)+(E2<>$B$1:$D$1))))))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


Put the formula in F2, confirm with Control+Shift+Enter, then drag to the rest of the cells in the table. Also note that this works if your percentages are all 1 digit (.6). If you go to 2 digits, find the 10 in the formula (3 places) and replace it with 100.

This should prevent the same manufacturer more than 2 weeks in a row, and still keep the same ratios. If you want a different list, just press F9. This example shows no MFG A for apples, which is possible. Adding more constraints might push this into the realm of VBA though.

Let me know if this helps.
 

artikyulashun

New Member
Joined
Aug 21, 2012
Messages
41
Thank you Eric.

This is highly creative and effective. Greatly appreciate the work that went into this.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,926
Messages
5,447,336
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top