# VBA Macro Help

#### rohankekatpure1987

##### New Member
I'm trying to perform segmentation of fruits into 4 quadrants based on sales Volume and CoV using Macro. Cumulative volume is comulative addition of volume. Below is my sample data set :

 Volume Cum Vol CoV Apple 30 30 0.3 Banana 20 50 0.4 Strawberry 19 69 0.2 Watermelon 10 79 0.3 Mango 6 85 0.3 Grapes 4 89 0.3 Orange 4 93 0.11 Pineapple 4 97 0.2 Rasberry 1 98 0.3 Cherry 2 100 0.5

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

*Volume is arranged in descending order always to calculate cumulative volume column.

Now the rules for segmentation are as follows:
1) I wish to have 4 quadrants (Nomenclature:Volume: A and B, COV: X and Y):

AX> High-Volume High-CoV,
AY> High Volume- Low CoV
BX>Low Volume- High CoV,
BY>Low Volume-Low CoV

A: Top 70% volume as A
B: Bottom 30% as B
X: Top 30% as X
Y: Bottom 70% as Y

2) To divide the fruits, I need to have a threshold for volume and CoV for AX, AY, BX and BY.
E.G. Is Apple an AX ?

3) My rule of thumb says the following:
a) I fix the AX segment such that> AX: 20-25% count of items contributing to 70% of volume
In remaining AY, BX and BY Segment I try to divide the count of items equally based on volume. E.g. BX, BY and AY segments will have n items contributing to 10% volume in each segment.

At the click of button macro will work to generate the sample output as follows:

Sample Output:
 Volume Cum Vol CoV Volume Segment CoV Segment Apple 30 30 0.3 A X Banana 20 50 0.4 A Y Strawberry 19 69 0.2 A X Watermelon 10 79 0.3 B X Mango 6 85 0.3 B X Grapes 4 89 0.3 B X Orange 4 93 0.11 B X Pineapple 4 97 0.2 B X Rasberry 1 98 0.3 B X Cherry 2 100 0.5 B Y

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

As seen above, Apple Banana and Strawberry contribute of 69% volume and hence in AX segment when I decide the thresholds for A as 70% and X as 0.3. Please note the above sample does not include equal distribution across other quadrants.

So, we need a work on the following:

1) Intelligently, identify thresholds for segments AX, BX, BY, AY
2) Create columns Volume segment and CoV segment based on the thresholds

Thanks for your help !!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I'm trying to perform segmentation of fruits into 4 quadrants based on sales Volume and CoV using Macro. Cumulative volume is comulative addition of volume. Below is my sample data set :

 Volume Cum Vol CoV Apple 30 30 0.3 Banana 20 50 0.4 Strawberry 19 69 0.2 Watermelon 10 79 0.3 Mango 6 85 0.3 Grapes 4 89 0.3 Orange 4 93 0.11 Pineapple 4 97 0.2 Rasberry 1 98 0.3 Cherry 2 100 0.5

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

*Volume is arranged in descending order always to calculate cumulative volume column.

Now the rules for segmentation are as follows:
1) I wish to have 4 quadrants (Nomenclature:Volume: A and B, COV: X and Y):

AX> High-Volume High-CoV,
AY> High Volume- Low CoV
BX>Low Volume- High CoV,
BY>Low Volume-Low CoV

A: Top 70% volume as A
B: Bottom 30% as B
X: Top 30% as X
Y: Bottom 70% as Y

2) To divide the fruits, I need to have a threshold for volume and CoV for AX, AY, BX and BY.
E.G. Is Apple an AX ?

3) My rule of thumb says the following:
a) I fix the AX segment such that> AX: 20-25% count of items contributing to 70% of volume
In remaining AY, BX and BY Segment I try to divide the count of items equally based on volume. E.g. BX, BY and AY segments will have n items contributing to 10% volume in each segment.

At the click of button macro will work to generate the sample output as follows:

Sample Output:
 Volume Cum Vol CoV Volume Segment CoV Segment Apple 30 30 0.3 A X Banana 20 50 0.4 A Y Strawberry 19 69 0.2 A X Watermelon 10 79 0.3 B X Mango 6 85 0.3 B X Grapes 4 89 0.3 B X Orange 4 93 0.11 B X Pineapple 4 97 0.2 B X Rasberry 1 98 0.3 B X Cherry 2 100 0.5 B Y

<tbody>
</tbody>

As seen above, Apple Banana and Strawberry contribute of 69% volume and hence in AX segment when I decide the thresholds for A as 70% and X as 0.3. Please note the above sample does not include equal distribution across other quadrants.

So, we need a work on the following:

1) Intelligently, identify thresholds for segments AX, BX, BY, AY
2) Create columns Volume segment and CoV segment based on the thresholds

Thanks for your help !!

Replies
1
Views
300
Replies
0
Views
127
Replies
1
Views
192
Replies
1
Views
1K
Replies
1
Views
232

1,216,487
Messages
6,130,944
Members
449,608
Latest member
jacobmudombe

### 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?

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