[Resolved] Percentile calculation

Joseph_Sliker

Hi;

I wonder if anyone has worked this out before...I'd appreciate your help.

I am trying to write php code that will help me with percentiles.

I'm pulling scores out of a MySQL database and I already understand how to do good stuff with the MySQL query like

COUNT(IF(TESTPHASE='FS',ORF_MWRC, Null)) AS A_N,
AVG(IF(TESTPHASE='FS',ORF_MWRC, Null)) AS A_MEAN,
STDDEV(IF(TESTPHASE='FS',ORF_MWRC, Null)) AS A_SD,
(COUNT(IF(TESTPHASE='FS',ORF_MWRC, Null))/2) AS A_N_HALF

But how do I figure out which score is at the nth percentile? I'm familiar with the basic statistical formulas, but has anyone written any php functions or scripts that would help me figure out how to code this?

Thanks for any suggestions.

Joseph_Sliker

Well, since I got no responses, I had to slog away at this one on my own....and I actually figured it out!

What I really needed at the moment was QUARTILE calculation. Here is code that actually works. I compared the results from this code with results for several sets of data in MS Excel and the results are identical, so if Excel does it right, this appears to be just as good.

Just note: if you are using a data array, make sure that the values in the array are already sorted in ascending order.

/* (This first section has to do with doing the calculation based on query results)
//Get variable from post data
$SCHOOLID = $HTTP_GET_VARS['SCHOOLID'];
$GRADE = $HTTP_GET_VARS['GRADE'];
$ROOM = $HTTP_GET_VARS['ROOM'];
$TESTPHASE = $HTTP_GET_VARS['TESTPHASE'];
$LANG = $HTTP_GET_VARS['LANG'];

// Set the variables for the database access:
$Host = "localhost";
$User = "USER";
$Password = "PASSWORD";
$DBName = "DBNAME";

$TableName = "TABLE";
$FIELD1 = 'FIELD1';

$Link = mysql_connect ($Host, $User, $Password);

//Query to get just one column
$Query = "SELECT $FIELD1 AS SCORE from $TableName WHERE SCHOOLID='$SCHOOLID' AND GRADE='$GRADE' AND ROOM='$ROOM' AND TESTPHASE='$TESTPHASE' AND LANG='$LANG' ORDER BY FIELD1 LIMIT $LIMIT";

mysql_select_db($DBName);
$Result= mysql_query($Query, $Link);

//Put the results into an array called "$ScoreArray"
while ($Row=mysql_fetch_array($Result)) { 
  $ScoreArray[] = $Row[SCORE];
}
mysql_close ($Link);
*/
//!!!!!!---IMPORTANT REMINDER: THESE FORMULAS ASSUME THAT THE VALUES IN THE ARRAY HAVE ALREADY BEEN SORTED IN ASCENDING ORDER---!!!!!!
//========demo data: delete this if you want to use other/query data======
$ScoreArray = array(28,32,41,55,67,73,85,85,85,87,91,112,126,140,143,152,157,160,168);
//==============================end demo data=============================

//KEEP: This counts the number of elements in the array
$n = count($ScoreArray);

//KEEP: take 1 away from N because element indexes start at 0
$top_n= $n-1;

//find the top (highest value) score (the array was sorted in the original query)
$Q4=$ScoreArray[$top_n];

//find the bottom (lowest value) score
$Q0=$ScoreArray[0];

//for the 3rd quartile
$k_3=(.75*($top_n));
$K3=floor($k_3);
$f3=($k_3-$K3);
$Q3=$ScoreArray[$K3]+($f3*($ScoreArray[$K3+1]-$ScoreArray[$K3]));

//for the 2nd quartile (median)
$k_2=(.5*($top_n));
$K2=floor($k_2);
$f2=($k_2-$K2);
$Q2=$ScoreArray[$K2]+($f2*($ScoreArray[$K2+1]-$ScoreArray[$K2]));

//for the 1st quartile
$k_1=(.25*($top_n));
$K1=floor($k_1);
$f1=($k_1-$K1);
$Q1=$ScoreArray[$K1]+($f1*($ScoreArray[$K1+1]-$ScoreArray[$K1]));

print ("<p>\n");
print ("Q4 score: $Q4<br>\n");
print ("Q3 score: $Q3<br>\n");
print ("Q2 score: $Q2<br>\n");
print ("Q1 score: $Q1<br>\n");
print ("Q0 score: $Q0<br>\n");

</body>
</html>