Rank only those product which contribute to 75% of total sales.

[Dali1962]

I have a list of products (200 approx)
I want to rank only those which contribute to 75% of total sales.

Is it possible to do this only with help of simple formula?

Sorting would not be feasible as i want to make it automatic.

Currently im using below formula which involves Percentile, it gives me an apporx answer but not exact.

=IF(IFERROR(RANK.EQ(I2,$I$2:$I$200),0)<=COUNTIF($I$2:$I$200,">"&PERCENTILE($I$2:$I$200,$A$2))+1,IFERROR(RANK.EQ(I2,$I$2:$I$200),""),"")

A2 cell contains - 75%

Kindly Help.

Thanks.

 

[Hui]

Dali

Firstly, Welcome to the Chandoo.org Forums

Can you please attach a sample file?

 

[Dali1962]

please find attached file.

 

[r2c2]

please find attached file.

Hi there

I am confused. When you rank only products that contribute to 75% total sales, wouldn't the rank be same as regular rank (ie rank among all products)? the products that add up to bottom 25% of sales are always ranked lower. What am I missing here?

 

[Dali1962]

yes, what you said is true. but i'm making a model, so i only want to rank products which contribute to 75% of total sales as later on the model will only display those products which are ranked and will ignore those which are not ranked.

I hope i'm able to put my thoughts properly for you to understand

 

[p45cal]

It may not be the most elegant, nor shortest formula, but it was the first one I got to which seemed to work; in your sample file, in cell C2 ARRAY-ENTER this formula:

=IF(SUM(IF(IFERROR(RANK.EQ($B$2:$B$198,$B$2:$B$198),999)<=IFERROR(RANK.EQ(B2,$B$2:$B$198),999),$B$2:$B$198,0))<SUM($B$2:$B$198)*0.75,RANK.EQ(B2,$B$2:$B$198),"")

Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.
Copy down.

 

[p45cal]

It may not be the most elegant, nor shortest formula, but

…and of course there's an improvement; normally Enter this in C2 and copy down:

=IFERROR(IF((SUMPRODUCT(--($B$2:$B$198>=B2),$B$2:$B$198)/$B$200)<=0.75,RANK.EQ(B2,$B$2:$B$198),""),"")

 

[Dali1962]

hey, this is an Array formula?
Sorry for the delayed reply

 

[Dali1962]

Can you send me the sample file where in you have tried the formula? it is showing error when i tried

 

[p45cal]

hey, this is an Array formula?

Msg#6 is, msg#7 is normally-entered.

 

[p45cal]

Can you send me the sample file where in you have tried the formula? it is showing error when i tried

When I get to the machine in a few hours. It'll just be your file with those formulae added.

 

[Dali1962]

yes..it is working...i did not sum at cell 200, so it was showing error.
Thanks a ton

 

[p45cal]

i did not sum at cell 200

My fault, you shouldn't need the sum there, the formula should be:

=IFERROR(IF((SUMPRODUCT(--($B$2:$B$198>=B2),$B$2:$B$198)/SUM($B$2:$B$198))<=0.75,RANK.EQ(B2,$B$2:$B$198),""),"")

 

[Dali1962]

oh...okay..thank you so much for efforts and time it was really helpful