Calculate top 20% of values from a list, sum product

[IWishToLearn]

Greetings,

I have about 100 reps that I have pulled sales data for. The sheets are set up thusly:

REP NAME MTD #1 MTD #2 MTD#3

What I need a formula to do is to sort through each column and find the top 20% of values, then sum the product into a header cell. I also need to find the bottom 20% of values and sum that product.

The idea is to figure out how much the top 20% of reps in each category is contributing and likewise how much is produced by the bottom 20%.

 

[excel_help_101]

What verson do you have? if you have 2007 or 2010 you can do the 20% with colors using conditional formatting then they can be could sorted by color

 

[rxp409]

I'm not totally sure i understand, but maybe for each column, do a sumif (columnrange, >percentile(columnrange,0.8)) would sum the top 20%. That's not the correct syntax though, because the > condition needs to be in "" i think.

 

[IWishToLearn]

I'm not totally sure i understand, but maybe for each column, do a sumif (columnrange, >percentile(columnrange,0.8)) would sum the top 20%. That's not the correct syntax though, because the > condition needs to be in "" i think.

I'll try this and will post results.

 

[Robert Mika]

Excel Workbook
	B	C	D
2	1	19	Top
3	9	3	bottom
4	3
5	4
6	7
7	6
8	5
9	8
10	2
11	10
Sheet1

 

[IWishToLearn]

Sheet1

*	B	C	D
2	1	19	Top
3	9	3	bottom
4	3	*	*
5	4	*	*
6	7	*	*
7	6	*	*
8	5	*	*
9	8	*	*
10	2	*	*
11	10	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula C2 =SUMPRODUCT(--($B$2:$B$11>=PERCENTILE($B$2:$B$11,0.8)),--($B$2:$B$11)) C3 =SUMPRODUCT(--($B$2:$B$11<=PERCENTILE($B$2:$B$11,0.2)),--($B$2:$B$11)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

This formula works phenomenal with one problem. Some of the cells have a blank "" cell due to the rep not having any sales data for the month, and the formula errors when those cells are included. How can I have the formula ignore those cells?

 

[Robert Mika]

Try:
(the SUMPRODUCT yell #VALUE for any formula empty cell- I hope that's what you were referring to, so use SUM(IF..)
Excel 2010

	A	B	C	D	E
1	1		19		#VALUE!
2	9		3
3	3
4	4
5	7
6	6
7	5
8
9	2
10	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E1 =SUMPRODUCT(--($A$1:$A$10>=PERCENTILE($A$1:$A$10,0.8)),--($A$1:$A$10)) A8 =IF(D8="","","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C1 {=SUM(IF($A$1:$A$10>=PERCENTILE($A$1:$A$10,0.8),$A$1:$A$10))} C2 {=SUM(IF($A$1:$A$10<=PERCENTILE($A$1:$A$10,0.2),$A$1:$A$10))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[IWishToLearn]

Try:
(the SUMPRODUCT yell #VALUE for any formula empty cell- I hope that's what you were referring to, so use SUM(IF..)
Excel 2010

	A	B	C	D	E
1	1		19		#VALUE!
2	9		3
3	3
4	4
5	7
6	6
7	5
8
9	2
10	10

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula E1 =SUMPRODUCT(--($A$1:$A$10>=PERCENTILE($A$1:$A$10,0.8)),--($A$1:$A$10)) A8 =IF(D8="","","") <TBODY> </TBODY>

<TBODY>
</TBODY>

Array Formulas Cell Formula C1 {=SUM(IF($A$1:$A$10>=PERCENTILE($A$1:$A$10,0.8),$A$1:$A$10))} C2 {=SUM(IF($A$1:$A$10<=PERCENTILE($A$1:$A$10,0.2),$A$1:$A$10))} <TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Exactly what I needed. THANK YOU!

 

[Robert Mika]

Exactly what I needed. THANK YOU!

You are welcome.
Thanks for feedback.

 

[tusharm]

Create a PivotTable and look at the Value Filter 'Top 10' settings. For example, you can get Top 20 items or Bottom 20 items, both of which would correspond to 20% of the 100 sales people. You can also get Top 20% by sales, and Bottom 20% by sales, in which case, the PT will show the top (or bottom) sales people whose sales represent 20% of the total sales.

Greetings,

I have about 100 reps that I have pulled sales data for. The sheets are set up thusly:

REP NAME MTD #1 MTD #2 MTD#3

What I need a formula to do is to sort through each column and find the top 20% of values, then sum the product into a header cell. I also need to find the bottom 20% of values and sum that product.

The idea is to figure out how much the top 20% of reps in each category is contributing and likewise how much is produced by the bottom 20%.