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.

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

<TBODY>
</TBODY>

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

Yaz