CFLib.org – Common Function Library Project

getExcelPercentile(values, percentile[, inclusive])

Last updated December 27, 2013

author

Dan Bracuk

Version: 1 | Requires: CF8 | Library: UtilityLib

Description:
Returns the requested percentile using an equivalent to the MS Excel percentile() / percentile.exc() functions.

Return Values:
Returns a numeric that is the specified percentile score given the values in the passed-in array.

Example:

<cfscript>
numbers = [];
for (i=1; i <= randRange(1,20); i++){
	arrayAppend(numbers, randRange(1,1000));
}
percentile = randRange(1,100);

result = getExcelPercentile(numbers, percentile);
writeDump(variables);
</cfscript>

Parameters:

Name Description Required
values Array of numeric values to return percentile for. Yes
percentile Percentile to calculate (0-100). Yes
inclusive Whether to perform an inclusive (percentile()) or exclusive (percentile.exc()) calculation. Will throw a PercentileOutOfBounds exception if using exclusive percentile and 'percentile' falls outwith 1/(n+1) and n/(n+1), where 'n' is the length of the values array. No

Full UDF Source:

<!---
 Returns the requested percentile()
 v0.9 by Dan Bracuk
 v1.0 by Adam Cameron. Adjusting slightly to more-closely replicate the Excel functions
 v1.1 by Adam Cameron. Fixing typo I introduced into v1.0
 
 @param values 	 Array of numeric values to return percentile for. (Required)
 @param percentile 	 Percentile to calculate (0-100). (Required)
 @param inclusive 	 Whether to perform an inclusive (percentile()) or exclusive (percentile.exc()) calculation. Will throw a PercentileOutOfBounds exception if using exclusive percentile and 'percentile' falls outwith 1/(n+1) and n/(n+1), where 'n' is the length of the values array. (Optional)
 @return Returns a numeric that is the specified percentile score given the values in the passed-in array. 
 @author Dan Bracuk (bracuk@pathcom.com) 
 @version 1.1, December 27, 2013 
--->
<cffunction name="getExcelPercentile" returntype="numeric" output="false" hint="Equivalent to the MS Excel percentile() / percentile.exc() functions.">
	<cfargument name="values" type="array" required="true" hint="Array of numeric values to return percentile for.">
	<cfargument name="percentile" type="numeric" required="true" hint="Percentile to calculate (0-100).">
	<cfargument name="inclusive" type="boolean" required="false" default="true" hint="Whether to perform an inclusive (percentile()) or exclusive (percentile.exc()) calculation. Will throw a PercentileOutOfBounds exception if using exclusive percentile and 'percentile' falls outwith 1/(n+1) and n/(n+1), where 'n' is the length of the values array.">

	<cfscript>
		var rangeArray = arguments.values;
		var rangePoint = 0;
		var integerPart = 0;
		var decimalPart = 0;
		var returnValue = 0;
		var pctile = arguments.percentile * .01;
		var numberOfValues = arrayLen(arguments.values);

		arraySort(rangeArray, "numeric");

		if (!inclusive) {
			rangePoint = (numberOfValues - 1) * pctile + 1;
		}
		else {
			rangePoint = (numberOfValues + 1) * pctile;
		}

		if (rangePoint > numberOfValues || rangePoint < 1){
			throw(type="PercentileOutOfBounds", message="Percentile out of bounds", detail="PERCENTILE falls outwith 1/(n+1) and n/(n+1), where 'n' is the length of the VALUES array.");
		}

		if (rangePoint > numberOfValues) {
			rangePoint = numberOfValues;
		}
		else if (rangePoint < 1) {
			rangePoint = 1;
		}

		// if rangePoint is an integer, return that array element
		if (round(rangePoint) == rangePoint) {
			returnValue = rangeArray[rangePoint] ;
		}
		else {
			integerPart = int(rangePoint);
			decimalPart = rangePoint - integerPart;
			returnValue = rangeArray[integerPart] + decimalPart * (rangeArray[integerPart + 1] - rangeArray[integerPart]);
		}
		return returnValue;
	</cfscript>
</cffunction>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson