#### rohankekatpure1987

##### New Member

- Joined
- Oct 28, 2015

- Messages
- 34

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

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