Identify Bottom 5% of values within a given category WITHOUT sorting - Excel 2010

[NellsVanells]

Hello,

I've searched everywhere for this solution but have thus far come up empty handed .

I would like to build an if statement that identifies the bottom 5% of contributors within each Category Code and Tier combination without having to manually sort the data and do cumulative calculations. Each Category Code/Tier combination is to be treated as a separate universe. For example, values belonging to Category Code "120" & Tier "Super" will get compared against each other only and the bottom 5% within this combination identified.

Below is an example with data in A1:C20

[TABLE="width: 330"]
<tbody>[TR]
[TD]Category Code[/TD]
[TD]Tier[/TD]
[TD]$ Sales[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]22,827[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,956[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,342[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]16,976[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]15,511[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]13,095[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]12,820[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Value[/TD]
[TD]12,753[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Value[/TD]
[TD]12,380[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]32,120[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]19,535[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,606[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,017[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]15,597[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]14,621[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]12,901[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]12,778[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]12,619[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]12,257[/TD]
[/TR]
</tbody>[/TABLE]


Any help or ideas would be greatly appreciated!

Thank you.

 

[anglais428]

what is your expected result from the above?

 

[NellsVanells]

Hi anglais428,

Thank you for your reply. The expected result would identify which values fall in the bottom 5% of each category/tier combination. Here is the expected result done manually by sorting the data first. To simply things I have made some values in my example very small so they clearly fall in the bottom 5% of each combination:

[TABLE="width: 432"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Category Code[/TD]
[TD]Tier[/TD]
[TD]$ Sales[/TD]
[TD]Bottom 5%[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]32,120[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]22,827[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Super[/TD]
[TD]1,000[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,956[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]17,606[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]750[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]700[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]16,976[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]15,597[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]1,500[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]15,511[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]14,621[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Value[/TD]
[TD]1,500[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]12,820[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]600[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]500[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Super[/TD]
[TD]300[/TD]
[TD="align: center"] TRUE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Value[/TD]
[TD]12,753[/TD]
[TD="align: center"] FALSE[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Value[/TD]
[TD]150[/TD]
[TD="align: center"] TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see the values with "TRUE" indicate the bottom 5% within each combination. For example, in the 300/Super combination, there are 4 Values which add up to 14,200. Within this combination we see that 3 values fall within the bottom 5% of sales (600, 500, 300).

I'm hopeful this result can be achieved without sorting the data first as the data set I'm working with constantly changes and I don't want to re-sort every time. I have no issue in adding additional columns to achieve this result. I would imagine adding a cumulative sum column using sumifs would help.


I hope this is clearer and thanks again!

 

[FormR]

Give this a try:

Excel Workbook
	A	B	C	D
1	Category Code	Tier	$ Sales	Bottom 5%
2	120	Super	32,120	FALSE
3	120	Super	22,827	FALSE
4	120	Super	1,000	TRUE
5	120	Value	17,956	FALSE
6	120	Value	17,606	FALSE
7	120	Value	750	TRUE
8	120	Value	700	TRUE
9	200	Super	16,976	FALSE
10	200	Super	15,597	FALSE
11	200	Super	1,500	TRUE
12	200	Value	15,511	FALSE
13	200	Value	14,621	FALSE
14	200	Value	1,500	TRUE
15	300	Super	12,820	FALSE
16	300	Super	600	TRUE
17	300	Super	500	TRUE
18	300	Super	300	TRUE
19	300	Value	12,753	FALSE
20	300	Value	150	TRUE
Sheet1

 

[NellsVanells]

Hi FormR,

Upon further experimentation with the formula you provided, I've found that it does not quite achieve the goal I'm looking for. This was basically my fault for providing an over simplified example data set. The formula you provided does indicate which values are under 5% within it's given combination however it does not accurately identify the bottom 5% of cumulative sales contributors within it's combination. Consider the following data set:
[TABLE="width: 719"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Category Code[/TD]
[TD]Tier[/TD]
[TD]$ Sales[/TD]
[TD]Bottom 5% Formula[/TD]
[TD]Share Within Combination[/TD]
[TD]Cummulative Share Within Combination[/TD]
[TD]Adjusted Bottom 5%[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]20,000[/TD]
[TD="align: center"] FALSE[/TD]
[TD]60.15%[/TD]
[TD]60.15%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]11,000[/TD]
[TD="align: center"] FALSE[/TD]
[TD]33.08%[/TD]
[TD]93.24%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]899[/TD]
[TD="align: center"] TRUE[/TD]
[TD]2.70%[/TD]
[TD]95.94%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]700[/TD]
[TD="align: center"] TRUE[/TD]
[TD]2.11%[/TD]
[TD]98.05%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]650[/TD]
[TD="align: center"] TRUE[/TD]
[TD]1.95%[/TD]
[TD]100.00%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]18,000[/TD]
[TD="align: center"] FALSE[/TD]
[TD]70.49%[/TD]
[TD]70.49%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]2,000[/TD]
[TD="align: center"] FALSE[/TD]
[TD]7.83%[/TD]
[TD]78.32%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]1,800[/TD]
[TD="align: center"] FALSE[/TD]
[TD]7.05%[/TD]
[TD]85.37%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]700[/TD]
[TD="align: center"] TRUE[/TD]
[TD]2.74%[/TD]
[TD]88.11%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]690[/TD]
[TD="align: center"] TRUE[/TD]
[TD]2.70%[/TD]
[TD]90.81%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]678[/TD]
[TD="align: center"] TRUE[/TD]
[TD]2.66%[/TD]
[TD]93.47%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]425[/TD]
[TD="align: center"] TRUE[/TD]
[TD]1.66%[/TD]
[TD]95.13%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]421[/TD]
[TD="align: center"] TRUE[/TD]
[TD]1.65%[/TD]
[TD]96.78%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]412[/TD]
[TD="align: center"] TRUE[/TD]
[TD]1.61%[/TD]
[TD]98.39%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]410[/TD]
[TD="align: center"] TRUE[/TD]
[TD]1.61%[/TD]
[TD]100.00%[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

As you can see the column labeled "Bottom 5% Formula" is using the formula you provided which correctly identifies values below 5% within the combinations. What I'm looking for is what is contained in the column labeled "Adjusted Bottom 5%" where the "TRUE" statements are applied to those values which fall in the bottom cumulative 5% within each combination. For example, when you look at the 200/Super Combination, only the values of 421,412, & 410 fall within the bottom cumulative 5% whereas the sumifs formula provided includes many more values as "TRUE". The data above has been sorted to illustrate what is needed. I'm hopeful there is a solution that does not involve sorting and doing cumulative calculations every time. I'm open to any other suggestions on how this can be achieved with formulas.

I appreciate your help with this! Thank you!

 

[vogel997]

[TABLE="width: 1193"]
<colgroup><col width="64" style="width: 48pt;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="64" style="width: 48pt;"> <col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;" span="3"> <col width="788" style="width: 591pt; mso-width-source: userset; mso-width-alt: 28818;"> <tbody>[TR]
[TD="width: 64, bgcolor: white"]Category Code[/TD]
[TD="width: 105, bgcolor: white"]Tier[/TD]
[TD="width: 133, bgcolor: white"]$ Sales[/TD]
[TD="width: 64, bgcolor: white"]Bottom 5% Formula[/TD]
[TD="width: 132, bgcolor: white"]Share Within Combination[/TD]
[TD="width: 112, bgcolor: white"]Cummulative Share Within Combination[/TD]
[TD="width: 64, bgcolor: white"]Adjusted Bottom 5%[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 788, bgcolor: white"]try this[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]120[/TD]
[TD="width: 105, bgcolor: white"]Value[/TD]
[TD="width: 133, bgcolor: white"]20,000[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 132, bgcolor: white"]60.15%[/TD]
[TD="width: 112, bgcolor: white"]60.15%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]=(C2/SUMPRODUCT(--(A2=$A$2:$A$17),$C$2:$C$17))<0.05[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]120[/TD]
[TD="width: 105, bgcolor: white"]Value[/TD]
[TD="width: 133, bgcolor: white"]11,000[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 132, bgcolor: white"]33.08%[/TD]
[TD="width: 112, bgcolor: white"]93.24%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]120[/TD]
[TD="width: 105, bgcolor: white"]Value[/TD]
[TD="width: 133, bgcolor: white"]899[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]2.70%[/TD]
[TD="width: 112, bgcolor: white"]95.94%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]120[/TD]
[TD="width: 105, bgcolor: white"]Value[/TD]
[TD="width: 133, bgcolor: white"]700[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]2.11%[/TD]
[TD="width: 112, bgcolor: white"]98.05%[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]120[/TD]
[TD="width: 105, bgcolor: white"]Value[/TD]
[TD="width: 133, bgcolor: white"]650[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]1.95%[/TD]
[TD="width: 112, bgcolor: white"]100.00%[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 105, bgcolor: white"] [/TD]
[TD="width: 133, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 132, bgcolor: white"] [/TD]
[TD="width: 112, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]18,000[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 132, bgcolor: white"]70.49%[/TD]
[TD="width: 112, bgcolor: white"]70.49%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]2,000[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 132, bgcolor: white"]7.83%[/TD]
[TD="width: 112, bgcolor: white"]78.32%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]1,800[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 132, bgcolor: white"]7.05%[/TD]
[TD="width: 112, bgcolor: white"]85.37%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]700[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]2.74%[/TD]
[TD="width: 112, bgcolor: white"]88.11%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]690[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]2.70%[/TD]
[TD="width: 112, bgcolor: white"]90.81%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]678[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]2.66%[/TD]
[TD="width: 112, bgcolor: white"]93.47%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]425[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]1.66%[/TD]
[TD="width: 112, bgcolor: white"]95.13%[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]421[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]1.65%[/TD]
[TD="width: 112, bgcolor: white"]96.78%[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]412[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]1.61%[/TD]
[TD="width: 112, bgcolor: white"]98.39%[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]200[/TD]
[TD="width: 105, bgcolor: white"]Super[/TD]
[TD="width: 133, bgcolor: white"]410[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 132, bgcolor: white"]1.61%[/TD]
[TD="width: 112, bgcolor: white"]100.00%[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

What I'm looking for is what is contained in the column labeled "Adjusted Bottom 5%"

Hi, give this a try:

Excel Workbook
	A	B	C	D	E	F	G
1	Category Code	Tier	$ Sales	Bottom 5% Formula	Share Within Combination	Cummulative Share Within Combination	Adjusted Bottom 5%
2	120	Value	20,000	FALSE	60.15%	60.15%	FALSE
3	120	Value	11,000	FALSE	33.08%	93.24%	FALSE
4	120	Value	899	TRUE	2.70%	95.94%	FALSE
5	120	Value	700	TRUE	2.11%	98.05%	TRUE
6	120	Value	650	TRUE	1.95%	100.00%	TRUE
7	200	Super	18,000	FALSE	70.49%	70.49%	FALSE
8	200	Super	2,000	FALSE	7.83%	78.32%	FALSE
9	200	Super	1,800	FALSE	7.05%	85.37%	FALSE
10	200	Super	700	TRUE	2.74%	88.11%	FALSE
11	200	Super	690	TRUE	2.70%	90.81%	FALSE
12	200	Super	678	TRUE	2.66%	93.47%	FALSE
13	200	Super	425	TRUE	1.66%	95.13%	FALSE
14	200	Super	421	TRUE	1.65%	96.78%	TRUE
15	200	Super	412	TRUE	1.61%	98.39%	TRUE
16	200	Super	410	TRUE	1.61%	100.00%	TRUE
Sheet1

 

[NellsVanells]

Hello FormR,

Thank you for all your help regarding my question. The formula you provided below does work provided that the data is pre sorted in descending order. I believe I have found a solution that works for my needs.
[TABLE="width: 554"]
<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Category Code[/TD]
[TD]Tier[/TD]
[TD]$ Sales[/TD]
[TD]Rank Within Combo[/TD]
[TD]Cum Sales % Within Combo[/TD]
[TD]Bottom 5%[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]20,000[/TD]
[TD]1[/TD]
[TD]60.15%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]11,000[/TD]
[TD]2[/TD]
[TD]93.24%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]899[/TD]
[TD]3[/TD]
[TD]95.94%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]700[/TD]
[TD]4[/TD]
[TD]98.05%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]Value[/TD]
[TD]650[/TD]
[TD]5[/TD]
[TD]100.00%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]18,000[/TD]
[TD]1[/TD]
[TD]70.49%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]2,000[/TD]
[TD]2[/TD]
[TD]78.32%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]1,800[/TD]
[TD]3[/TD]
[TD]85.37%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]700[/TD]
[TD]4[/TD]
[TD]88.11%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]690[/TD]
[TD]5[/TD]
[TD]90.81%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]678[/TD]
[TD]6[/TD]
[TD]93.47%[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]425[/TD]
[TD]7[/TD]
[TD]95.13%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]421[/TD]
[TD]8[/TD]
[TD]96.78%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]412[/TD]
[TD]9[/TD]
[TD]98.39%[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Super[/TD]
[TD]410[/TD]
[TD]10[/TD]
[TD]100.00%[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

The first thing I did was have a formula which ranked the values within each combo:
=SUMPRODUCT(($A$2:$A$17=$A2)*($B$2:$B$17=$B2)*($C2<$C$2:$C$17))+SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C2=$C2))

The second thing needed was a formula that did the cum % within each combo using the ranking as one of the criteria:
=SUMPRODUCT(($A$2:$A$17=$A2)*($B$2:$B$17=$B2)*($D$2:$D$17<=$D2)*($C$2:$C$17))/SUMPRODUCT(($A$2:$A$17=$A2)*($B$2:$B$17=$B2)*($C$2:$C$17))

The final step was a basic if statement identifying the bottom 5% within each combo:
=IF(E2>0.95,"TRUE","FALSE")


Using this method, I won't have to pre sort the data. Thank you for everyone who contributed to this post! Cheers!

 

[FormR]

Using this method, I won't have to pre sort the data.!

Cool, I'm glad you got a working solution.

I believe the below behaves in the same way as your current set-up and should be a little more efficient.

Excel Workbook
	A	B	C	D	E	F
1	Category Code	Tier	$ Sales	Rank Within Combo	Cum Sales % Within Combo	Bottom 5%
2	120	Value	20,000	1	60%	FALSE
3	120	Value	11,000	2	93%	FALSE
4	120	Value	899	3	96%	TRUE
5	120	Value	700	4	98%	TRUE
6	120	Value	650	5	100%	TRUE
7	200	Super	18,000	1	70%	FALSE
8	200	Super	2,000	2	78%	FALSE
9	200	Super	1,800	3	85%	FALSE
10	200	Super	700	4	88%	FALSE
11	200	Super	690	5	91%	FALSE
12	200	Super	678	6	93%	FALSE
13	200	Super	425	7	95%	TRUE
14	200	Super	421	8	97%	TRUE
15	200	Super	412	9	98%	TRUE
16	200	Super	410	10	100%	TRUE
Sheet1