Determine which percentile value falls into?

[parodytx]

I have a large column of data sorted into sectors of varying lengths. I would like assistance in crafting the VBA formula(e) which determine into which percentile the individual datum lies.
For example,

<CODE>Sector Value Percentile
A 1.024% ?
A 3.027% ?
A 4.487% top 33%
B 0.060% ?
B 0.598% ?
B 0.643% top 25%
B 0.160% ?
C 2.927% top 100%</CODE>
In Sector A, 4.487% is in the top 33% (as there are only 3 values), 0.643% in Sector B is in the top 25% and C would be 100% as there is only one value.

I am using Excel 2010 Windows 7 32 bit.

Thanks in advance for any help.</PRE>

 

[yazansalsa]

Hello,

this worked for me,

You need to custom sort on your "Sector" then on your "Value" A to Z , Smallest to largest respectively.

[TABLE="width: 737"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IF(A1<>A2,A1,"")</SPAN>
[/TD]
[TD]IF(A1<>A2,"top "& TEXT(1/ COUNTIF($A$3:$A$10,A1),"0.00%"),"")</SPAN>
[/TD]
[/TR]
[TR]
[TD]Sector</SPAN>
[/TD]
[TD]Value</SPAN>
[/TD]
[TD]Percentile</SPAN>
[/TD]
[TD]Max Value</SPAN>
[/TD]
[TD]Percentile</SPAN>
[/TD]
[/TR]
[TR]
[TD]A</SPAN>
[/TD]
[TD]1.02%</SPAN>
[/TD]
[TD]?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A</SPAN>
[/TD]
[TD]3.03%</SPAN>
[/TD]
[TD]?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A</SPAN>
[/TD]
[TD]4.49%</SPAN>
[/TD]
[TD]top 33%</SPAN>
[/TD]
[TD]4.49%</SPAN>
[/TD]
[TD]top 33.33%</SPAN>
[/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]0.06%</SPAN>
[/TD]
[TD]?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]0.16%</SPAN>
[/TD]
[TD]?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]0.60%</SPAN>
[/TD]
[TD]?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]0.64%</SPAN>
[/TD]
[TD]top 25%</SPAN>
[/TD]
[TD]0.64%</SPAN>
[/TD]
[TD]top 25.00%</SPAN>
[/TD]
[/TR]
[TR]
[TD]C</SPAN>
[/TD]
[TD]2.93%</SPAN>
[/TD]
[TD]top 100%</SPAN>
[/TD]
[TD]2.93%</SPAN>
[/TD]
[TD]top 100.00%</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


let me know if this is what you where looking for.

Yaz