Percentile Function of excel

[dali1962]

I have a list of sales data.

I want to list the names which contribute to 75% of all total sales.

how do I do that? is it possible to do it with percentile function??

Or just rank the names which contribute up till 75% of sales. highest contributing SKU will be ranked as 1st and so on. the last 25% percent do not need to be ranked.

is it possible to do so only with formulas and not macros?

Please help

Name	Sales
A	3405
B	4893
C	7920
D	4682
E	3082
F	9283
G	1038
Total	XX

<tbody>
</tbody>

 

[alexk7384]

If you have your table in range ("A1:B9") then you could just drag the following formula in range ("C2:C8"):

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'} span.s1 {color: #0057d6} span.s2 {color: #006107} </style>=IF(B2>PERCENTILE(B$2:B$8,75%),RANK.EQ($B2,$B$2:$B$8),"")

The 75% percentile in this case is 6,407, which is a midpoint between the second ranked value C - 7,920 and the third one B - 4,893. So you will have only two values ranked - F and C and the rest will have blanks next to them in column C.

 

[dali1962]

i guess its a little bit more tricky than this.

Name	Sales	PercentSales	Rank
A	3405	10%
B	4893	14%	3
C	7920	23%	2
D	4682	14%
E	3082	9%
F	9283	27%	1
G	1038	3%
Total	34303

<tbody>
</tbody>

As the highest selling products are F, C and B which contribute to approx 75% of total sales, i only want to rank them and ignore the rest. If i add D as well, it will contribute to 78% sales but i want the products which contribute only uptill 75% of sales.

Please help

 

[alexk7384]

I think this should solve it:

=IF(RANK.EQ($B2,$B$2:$B$8)<=COUNTIF($B$2:$B$8,">"&PERCENTILE.INC($B$2:$B$8,75%))+1,RANK.EQ($B2,$B$2:$B$8),"")

 

[dali1962]

Hey, I'm using this formula, but it is showing error

=IF(RANK.EQ(Sheet1!$D2,Sheet1!$J$2:$J$150)<=COUNTIF(Sheet1!$J$2:$J$150,">"&PERCENTILE.INC(Sheet1!$J$2:$J$150,75%))+1,RANK.EQ(Sheet1!$J2,Sheet1!$J$2:$J$150),"")

 

[dali1962]

Hey, Sorry, the formula is working but it is ranking almost all the sales SKU and not only those which contribute to 75% of sales.

 

[alexk7384]

I suppose it's because of this (should be column J, not D):

=IF(RANK.EQ(Sheet1!$D2,Sheet1!$J$2:$J$150)<=COUNTIF(Sheet1!$J$2:$J$150,">"&PERCENTILE.INC(Sheet1!$J$2:$J$150,75%))+1,RANK.EQ(Sheet1!$J2,Sheet1!$J$2:$J$150),"")

 

[dali1962]

It has got copy pasted wrongly.. i have used "J" itslef and not "D"

=IF(RANK.EQ(Sheet1!$J2,Sheet1!$J$2:$J$150)<=COUNTIF(Sheet1!$J$2:$J$150,">"&PERCENTILE.INC(Sheet1!$J$2:$J$150,75%))+1,RANK.EQ(Sheet1!$J2,Sheet1!$J$2:$J$150),"")

 

[alexk7384]

Strange - works for me! See in google sheets below:

https://docs.google.com/spreadsheets/d/14wls7Q09ZhDgdonRkWKdlxKmqUFkP2d5eNmodW7mLIk/edit?usp=sharing

 

[dali1962]

In my array selection, i have included blank cell as well..would it be cause of that?