formula to identify cells in top %

[JV0710]

Good day everyone

I apologise that I may have asked this question before, But I recently lost all my laptop and all backups ( and attemps to search for my previous posts show only 6 previous posts )

In the example shown:
In coulmn E, I am looking for a formula that will return the "% Contr", from column F, if it falls into the top 60% ( Or whatever % I put into cell E2 & E3 ) - Another variation would be to return a message ( Like : Good, Bad etc - If I wanted to marked the numbers that make up the bottom 25% for example)

Thanks for your help

test top80 perc.xls
	A	B	C	D	E
1					%Contrmakingupthe
2					Top
3	Product	Sales	%Grwth	%Contr	60%
4	Category1	13,558	0.00%	0.00%
5	Category2	1,183	0.00%	0.00%
6	Category3	129,595,201	-16.49%	30.48%	30.48%
7	Category4	166,860	279.58%	0.04%
8	Category5	14,604,475	36.21%	3.44%
9	Category6	276,546	2587.92%	0.07%
10	Category7	1,295	-85.91%	0.00%
11	Category8	122,834	149.71%	0.03%
12	Category9	125,237,206	-28.83%	29.46%	29.46%
13	Category10	7,984,811	31.55%	1.88%
14	Category11	4,977,496	3.42%	1.17%
15	Category12	7,755,973	46.23%	1.82%
16	Category13	20,497	0.00%	0.00%
17	Category14	55,083,866	11.21%	12.96%
18	Category15	6,295,733	31.29%	1.48%
19	Category16	54,712,449	9.44%	12.87%
20	Category17	6,524,887	-8.16%	1.53%
21	Category18	1,730,474	11.08%	0.41%
22	Category19	38,783	-18.43%	0.01%
23	Category20	0	-100.00%	0.00%
24	Category21	3,838,367	6031.77%	0.90%
25	Category22	6,181,686	-32.27%	1.45%
26	Total	425,164,180	-11.61%	100.00%
27
28	IncoulmnE,Iamlookingforaformulathatwillreturnthe"%Contr",fromcolumnF,ifitfallsintothetop60%(Orwhatever%IputintocellE2&E3)-Anothervariationwouldbetoreturnamessage(Like:Good,Badetc-IfIwantedtomarkedthenumbersthatmakeupthebottom25%forexample)
29
30
31
32
33
Sheet3

 

[DonkeyOte]

I'm not a formula whizz like some of these guys so the only way I can think to do it would be to add an additional column that pulls rank - say column F

F4 = RANK($D$4,$D$4:$D$25,0)

This would then show the relative position of that % in the range.

Then in E you could use a sumif type formula utilising the RANK position

E4 = $D4*(SUMIF($F$4:$F$25,"<"&$F4,$D$4:$D$25)<0.6)

What this is basically doing is calculating the accumulated percentage of the values preceeding the current rank and if less than 60% it multiplies the current percentage being reviewed by 1 thus returning that percentage in E -- if not it shows as 0.

You could obviously adjust / extend to show blank if that's your preference, else you could format col E accordingly such that 0 shows as blank.

 

[JV0710]

Thank you LAWS10

That works . . .

Ideally I do not want to use other columns - but this will do

Thanks again

JVN

 

[JV0710]

Laws10

Okay . . .

I got it to work without using an extra column by using the principle of your formula and modifying it to this:

In cell E4 ( copied down ), the formula is:
=IF(D4*(SUMIF(D$4:D$25,">"&D4,D$4:D$25)<$E$3)=0,"",D4*(SUMIF(D$4:D$25,">"&D4,D$4:D$25)<$E$3))

This does exactly what your formula was doing and hides the Zero values - in one column

But now I have a another problem:
the values that it is identifying as being in the top 60% are: D6 (30.48%) , D12 (29.46%) and D17 ( 12.96%).

That adds up to 72.9% - It is too high -

It should just bring back 30.48% and 29.46%, which is 59.94%

Can anyone help me do this ?

 

[Peter_SSs]

Well, you could try this "mouthful" until somebody comes up with the "easy way".

Excel Workbook
	D	E
2		Top
3	%*Contr	60%
4	0.00%
5	0.00%
6	30.48%	30.48%
7	0.04%
8	3.44%
9	0.07%
10	0.00%
11	0.03%
12	29.46%	29.46%
13	1.88%
14	1.17%
15	1.82%
16	0.00%
17	12.96%
18	1.48%
19	12.87%
20	1.53%
21	0.41%
22	0.01%
23	0.00%
24	0.90%
25	1.45%
26	100.00%
Top %

 

[JV0710]

Thanks Peter

This gets the result closer to the 60%.

If I change the % to 70% though, I get the same result and it does add the 12.96 % to get closer to the 70%.

I guess what i am looking for is to get within a certain acceptable percentage to the target % - let's say 10% either way. . .( If that makes sense ).

I like your formula though . . . I am going to spend the next few hours getting my head around it and playing out different scenarios with it.

Thanks Once again

JVN

LASW10 - I apologise for spelling your id wrong in my replies

 

[Peter_SSs]

But now I have a another problem:
the values that it is identifying as being in the top 60% are: D6 (30.48%) , D12 (29.46%) and D17 ( 12.96%).

That adds up to 72.9% - It is too high -

I thought this earlier statement meant that you did not want to exceed the given %. Are you saying that the selected items can exceed the % in E3 - you just want the total that is closest to E3 whether it is above or below?

If so, what would you want to happen if the E3 value is 70% and the top 3 numbers added to 68% and the top 4 numbers added to 72%?

 

[JV0710]

Thanks for reply Peter

Now you have me stumped . . . I did not think about that

Okay, so After some deliberation, the decision is to have the selected numbers total up to the closest number to what is in e3 ( whether above or below ), and if you have the situation with the where e3 is 70% and the one set of numbers add to 68% and another set adds up to 72%, then show the higher of the two i.e. the set that adds to 72%

JVN