VBA Macro Help

[rohankekatpure1987]

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

 

[rohankekatpure1987]

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

Can someone please help me with this question?