Quartile vs. Percentile Formula

[dolsontopb]

I used the Pth Percentile summary formula in a report. The result did not appear accurate. After some research and manual recalculation - there is a discrepancy in the Crystal calculation when I use the 75th percentile formula for my dataset. Therefore, I want to use 3rd quartile instead, hoping that would work as the result should be the same at 75th percentile. I've tried researching it on-line but cannot find a formula example for quartiles in Crystal. I am not a programmer - so no SQL or VBA skills here. I started using/creating databases in Access well over a decade ago, then moved to Crystal - fairly good at writing formulas (in excel too), but not an expert by any means. Any help you can offer to either (A) help me correct the percentile formula or (2) create a quartile formula - would be appreciated.

I've attached a screen shot of my design view and result - if that helps. What I am doing is creating a report that compares market salary data to our pay scale, to determine if we have any pay ranges that are not aligned with our market. I am calculating the median and 75th (or 3rd quartile) of the market set, then comparing that data to the min, mid, and max of the pay range for the benchmarked position. I am pulling the data from 2 excel spreadsheets - one contains the data for our organization and the other contains the market data.

THANK YOU!

 

[briangriffin]

There are optional parameters for the Pth Percentile function - are you using the one that identifies the appropriate group? For instance, in your example it should be something like:

PthPercentile (75,{SalaryField},{OrganizationalData.Position))

You probably have it right, but it's also the most likely explanation for your variances. I'm not aware of any quartile formula in Crystal that doesn't leverage the PthPercentile function.

 

[dolsontopb]

Thanks so much for your response. I was delayed in getting back to this project due to budget planning etc. Back at it now. My formula appears accurate as follows:
PthPercentile (75, {'ALL_DATA_'.Minimum}, {TOPBDATA_.TOPB Title})

When I take the same set of data and calculate the percentile in excel - the result is different than that generated from crystal. For example - the data is below, with result 75th percentile from Crystal and 75th percentile from Excel

$31,257
$37,453
$42,216
$33,661
Result Crystal 75th Percentile: $39,835
Result Excel 75th Percentile: $38,644

 

[briangriffin]

I got the same results you did both in Excel and Crystal, so my next thought was to write a custom function in CR to see if there was a difference. I used this link to create the formula:



Stepping through with your test data and a calculator, I got the same 75th percentile as the Crystal function.

The last sentence in the article is "The steps shown here demonstrate one way of calculating percentiles, but there are several other acceptable methods. Do not be too alarmed if your calculator or a friend gives you a value close to but different from what these steps would give."

So I can only assume that Crystal and Excel use different (but presumably equally valid) methods, and the greater the number of records then the lesser the difference between the two.

If you have a specific method that you need or want to use then you can still write a custom function. Post back if you need a hand doing that.

 

[dolsontopb]

Really appreciate your help on this. I have over 400 individual records grouped by benchmark positions to determine a target salary at the 75th percentile. I think I will footnote the variation in some form/fashion to cover any discrepancies that those who review the data may find if they rerun the numbers in excel. I was thinking this morning of trying to calculate it manually for a small group to see what I get - but would have to go back 25 years to high school stats to figure that out. I may also search some stat text books to see if I can pull a citation for the variation from there. Really appreciate someone else looking at this for me - I was thinking I was going crazy there for a while. Thanks again!!