Home : GPERC
Q10779 - INFO: GPERC
 GPERC returns the value corresponding to a certain percentile.

SYNTAX:

GPERC(VarNum,GroupOffset/StartDate,Grouping/EndDate, Percentile, Method) 

VarNum:  The variable number to get the percentile for.

GroupOffset/StartDate: Used with the Grouping parameter to set the dates to calculate the stat for.  Sets the number of Groups (set by the Grouping Parameter) to be added to set the dates.  See examples below. If this field is a date string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the start date to calculate the stat for.

Grouping/EndDate: Used to set which dates to calculate the stat for.  If the Grouping is a date literal string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the end date to calculate the stat for. Common Groupings are:

"D" Days
“M” Monthly 
"R" Report Dates
Click here for available Grouping options

Percentile: The percentile that should be looked up (0-100).

Method: The method to use when a percentile falls between 2 known percentiles. Enter a 1, 2, 3 or 4 depending on the method you wish to use (see below.)  All methods multiply the number of available samples by the percentile desired and divides by 100. (i.e., 31 samples * 90 Percentile/100 total percentile = 27.90) to calculate the "slot" to retrieve the value from.  The difference in the methods is how they use that number:

Method 1 (round up): Order the values from lowest to highest, then round the slot number UP to the nearest available slot and return the number contained within that slot. (Slot 28 in the example above) The slot number is always rounded up; this is in compliance with the corresponding EPA standards (http://www.doh.wa.gov/ehp/dw/ccr_material/ccrfaq.htm):

The following instructions (taken from Chapter 246-290-025 WAC, 40 CFR Section 141.80) are written using lead as the example, but they apply in the same way to copper results.

1. Put all of your lead results in increasing order, starting with the smallest value and ending with the largest value, including non-detects.  You'll probably have several non-detects listed first, followed by a few detections.  Make sure your detections are in increasing order.

2. Multiply the number of samples you took by 0.9. Round this result up to the next highest whole number.

3. Go to your ordered list of lead sample results and identify the sample that corresponds to the whole number from step 2 above.

4. The concentration of that sample is the 90th percentile.

Method 2 (average 2 numbers): Order the values from lowest to highest and average the two numbers in the slots on either side of the slot calculated. (Slots 27 and 28 would be averaged in the example above)  This method was implemented for special cases as stated in 40 CFR Section 141.80(c)(3)(iv) where "the 90th percentile is computed by taking the average of the highest and second highest concentrations" (see exerpt below).

Method 3 (Scientic Rounding): Order the values from lowest to highest, then round the slot number using using Scientific Rounding and return the number contained within that slot.

Method 4 (Interpolate):  Order the values from lowest to highest and interpolate the two numbers in the slots on either side of the slot calculated. (Slots 27 and 28 would be used and the number would be interpolated as .9 between the two numbers).  NOTE:  This is the technique the Excel Percentile function uses.

Method 5 (Interpolate, exclude first and last values): Order the values from lowest to highest,remove the first and last value, and interpolate the two numbers in the slots on either side of the slot calculated. NOTE:  This is the technique the Excel Percentile.EXC function uses.

Method 6 (Interpolate):  Order the values from lowest to highest and interpolate the two numbers in the slots on either side of the slot calculated. (Slots 27 and 28 would be used and the number would be interpolated as .9 between the two numbers).  NOTE:  Specified by Connecticut DPH and is slightly different Interpolation with Method 4.  Example to calc the 90th percentile of 7 samples:

7 * 90/100 = 6.3
Find the difference between the 6th (1.73) and 7th (1.95) ranked values:
1.95 - 1.73 = 0.22
Multiple .3 (the fractional part of the 6.3 above) times the difference:
0.3 * .22 = 0.066
Add 0.066 to the 6th ranked result:
1.73 + 0.066 = 1.796

In all methods, if an exact slot number is calculated, the number in the corresponding slot is returned.  Example, the 90th Percentile of 10 results in the value in the 9th slot regardless of method.

NOTES:

Not every percentage will correspond to a number in the set. IE just because 85 is a percent does not mean it has any numbers in the set associated with it. That is why the methods are present. They help 85 return a value associated with it even though there are none.

EXAMPLES:

 

GPERC(11, 1, "M", 12, 1)  Returns the next highest value associated with the percentile closest to 12 for the report month for variable 11.

GPERC(11, 1, "M", 13, 2)  Returns the average of 2 values which are associated with the closest high or equal percentile and the closest lower percentile.

Download Example Template: Help_GPERC_Examples.ss3 (Click here for download instructions)

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 2/18/2008 3:24 PM.
Last Modified on 7/25/2019 4:02 PM.
Last Modified by Scott Dorner.
Article has been viewed 9646 times.
Rated 7 out of 10 based on 9 votes.
Print Article
Email Article