Help calculating Excel Quartiles in SPSS

[Paul Mueller-3]

SPSS and Excel calculate breakpoints for quartiles in different ways.  This is problematic for an office of analysts where some use Excel for analysis and some use SPSS for analysis.  We have decided that the easiest solution is program SPSS to produce and table excel quartiles.  This way a task will have the same consistent quartile values no matter the analyst assigned to them.   Since this seems like a problem others might have encountered, I thought I would ask the list for advice on programming the excel algorithm into an SPSS macro that the community of users might benefit from.

 

Microsoft describes there algorithm at their knowledge base (http://support.microsoft.com/kb/103493)

1. Find the kth smallest member in the array of values, where:

       k=(quart/4)*(n-1))+1

                                       

If k is not an integer, truncate it but store the fractional portion (f) for use in step 3.

    quart = value between 0 and 4 depending on which quartile

            you want to find.

    n     = number of values in the array

                                       

2. Find the smallest data point in the array of values that is greater than the kth smallest, the (k+1)th smallest member.

 

3. Interpolate between the kth smallest and the (k+1)th smallest values:

      Output = a[k]+(f*(a[k+1]-a[k]))

 

      a[k]   = the kth smallest

      a[k+1] = the k+1th smallest

 

The 0th quartile is the minimum value, the 2nd quartile is the median, and the 4th quartile is the maximum value.  Those should be pretty easy to capture through a simple aggregation command.  I am struggling with programming a flexible process to calculate the 25^th percentile (1^st quartile) and 75^th percentile (3^rd quartile) where the program can adjust itself on the fly to arrays of different sizes.

 

When our office upgrades to the latest version of SPSS, potentially this problem could be solved in R since R and S-Plus use the same quartile algorithm as Excel, but we currently deploy SPSS 14 in our computing environment.

 

I would appreciate any help or insights others might have.

 

Thanks,

 

 

 

Paul D. Mueller, Ph.D.

Senior IR Analyst

Office of Institutional Research

210 Flanner Hall

Notre Dame, Indiana 46556

tel: [phone]

fax: [phone]

 

[Peck, Jon]

Have you considered using EXAMINE for the percentile calculations?  It offers 5 different algorithms, although only HAVERAGE is provided in the dialog box.

 

Regards,

Jon Peck

 

---

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Paul Mueller
Sent: Thursday, June 18, 2009 9:50 AM
To: [hidden email]
Subject: [SPSSX-L] Help calculating Excel Quartiles in SPSS

 

SPSS and Excel calculate breakpoints for quartiles in different ways.  This is problematic for an office of analysts where some use Excel for analysis and some use SPSS for analysis.  We have decided that the easiest solution is program SPSS to produce and table excel quartiles.  This way a task will have the same consistent quartile values no matter the analyst assigned to them.   Since this seems like a problem others might have encountered, I thought I would ask the list for advice on programming the excel algorithm into an SPSS macro that the community of users might benefit from.