Frequency of Product Mix

A

alibbye17

New Member
Joined
May 19, 2010
Messages
40
Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Can someone can help me out with a frequency formula (I believe that would work best here). <o:p></o:p>
<o:p></o:p>
What Im trying to figure out is the product mix of past data. I would like to look at each of the 20 products we offer and say, based on our past data (for a given year) if a customer purchases “product 1” within the same order there will be a x% chance they will purchase “product 2”, an x% chance they will purchase “product 3”,….., an x% chance they will purchase “product 20”.<o:p></o:p>
<o:p></o:p>
Data:<o:p></o:p>
<o:p></o:p>
Column A (Order Number) <o:p></o:p>
*Within the data, an order number would show as a duplicate indicating that multiple products were purchased.<o:p></o:p>
100<o:p></o:p>
100<o:p></o:p>
102<o:p></o:p>
103<o:p></o:p>
104<o:p></o:p>
104<o:p></o:p>
104<o:p></o:p>
104<o:p></o:p>
104<o:p></o:p>
105<o:p></o:p>
<o:p></o:p>
Column B (Year Purchased)<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2011<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2010<o:p></o:p>
2011<o:p></o:p>
<o:p></o:p>
Column C (Product)<o:p></o:p>
Prod 1<o:p></o:p>
Prod 2<o:p></o:p>
Prod 3<o:p></o:p>
Prod 4<o:p></o:p>
Prod 5<o:p></o:p>
Prod 5<o:p></o:p>
Prod 1<o:p></o:p>
Prod 1<o:p></o:p>
Prod 1<o:p></o:p>
Prod 6<o:p></o:p>
<o:p></o:p>
Column D (Quatity)<o:p></o:p>
1<o:p></o:p>
2<o:p></o:p>
1<o:p></o:p>
2<o:p></o:p>
1<o:p></o:p>
2<o:p></o:p>
3<o:p></o:p>
4<o:p></o:p>
1<o:p></o:p>
2<o:p></o:p>

If more of an explanantion is needed, please let me know
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
This may help, it excludes the order number though

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Order Number</td><td style=";">year</td><td style="text-align: right;;"></td><td style=";">vol</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;;">2010</td><td style=";">Prod 1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;;">2010</td><td style=";">Prod 2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">102</td><td style="text-align: right;;">2010</td><td style=";">Prod 3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">103</td><td style="text-align: right;;">2011</td><td style=";">Prod 4</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style="text-align: right;;">2010</td><td style=";">Prod 5</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style="text-align: right;;">2010</td><td style=";">Prod 5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style="text-align: right;;">2010</td><td style=";">Prod 1</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style="text-align: right;;">2010</td><td style=";">Prod 1</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style="text-align: right;;">2010</td><td style=";">Prod 1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">105</td><td style="text-align: right;;">2011</td><td style=";">Prod 6</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">Table to show Total Volume bought by year</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">Prod 1</td><td style=";">Prod 2</td><td style=";">Prod 3</td><td style=";">Prod 4</td><td style=";">Prod 5</td><td style=";">Prod 6</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2010</td><td style="text-align: right;;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2011</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=B$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=C$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=D$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=E$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=F$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G16</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$16</font>),--(<font color="Red">$D$3:$D$12=G$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=B$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=C$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=D$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=E$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=F$15</font>),$E$3:$E$12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G17</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$3:$C$12=$A$17</font>),--(<font color="Red">$D$3:$D$12=G$15</font>),$E$3:$E$12</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Thanks for the response Joneye. However, the key to this mix comparison is the order number. What you provided is a quantity count for each product for each year. However, what im trying to do is provide a quantity count (as a%) on all products when (for example) product 1 is included within the order.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
So (given the data):<o:p></o:p>
<o:p> </o:p>
Product 1 (2 Orders included this Product)<o:p></o:p>
<o:p> </o:p>
Of the 2 orders in 2010, the % another product would tag along within is:<o:p></o:p>
<o:p> </o:p>
Prod 1 (100%), Prod 2 (50%), Prod 3 (0%), Prod 4 (0%), Prod 5 (50%), etc….<o:p></o:p>
<o:p> </o:p>
I hope this example is clearer.<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

itsrich
  • Solved
Indirect Syntax - Outside the workbook
Replies
7
Views
233
itsrich
itsrich
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
317
Mouse7681
M
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
107
flashgordie
F
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
659
libelouli
L

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top