VBA Macro Help

rohankekatpure1987

New Member
Joined
Oct 28, 2015
Messages
34
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 :

VolumeCum VolCoV
Apple 30300.3
Banana 20500.4
Strawberry19690.2
Watermelon10790.3
Mango6850.3
Grapes4890.3
Orange4930.11
Pineapple4970.2
Rasberry1980.3
Cherry 21000.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:
VolumeCum VolCoVVolume Segment CoV Segment
Apple 30300.3AX
Banana 20500.4AY
Strawberry19690.2AX
Watermelon10790.3BX
Mango6850.3BX
Grapes4890.3BX
Orange4930.11BX
Pineapple4970.2BX
Rasberry1980.3BX
Cherry 21000.5BY

<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 !!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

rohankekatpure1987

New Member
Joined
Oct 28, 2015
Messages
34
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 :

VolumeCum VolCoV
Apple30300.3
Banana20500.4
Strawberry19690.2
Watermelon10790.3
Mango6850.3
Grapes4890.3
Orange4930.11
Pineapple4970.2
Rasberry1980.3
Cherry21000.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:
VolumeCum VolCoVVolume SegmentCoV Segment
Apple30300.3AX
Banana20500.4AY
Strawberry19690.2AX
Watermelon10790.3BX
Mango6850.3BX
Grapes4890.3BX
Orange4930.11BX
Pineapple4970.2BX
Rasberry1980.3BX
Cherry21000.5BY

<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 !!

Can someone please help me with this question?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

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
Top