quartiles in Excel and SPSS

[paulb...@my-deja.com]

We've run across an apparent difference in the way that Excel and SPSS
calculate percentiles and quartiles.

As a simple demonstration, take the series of numbers from 1 to 10.

Excel says that the 25th percentile is 3.25. SPSS says it is 2.75.

At least they both agree that the 50th percentile is 5.5.

For the 75th percentile, Excel says 7.75, and SPSS says 8.25.

Is one of these more correct than the other? If so, why?

Thanks.

Sent via Deja.com http://www.deja.com/
Before you buy.

[Rich Ulrich]

On Tue, 05 Dec 2000 19:55:49 GMT, paulb...@my-deja.com wrote:

> We've run across an apparent difference in the way that Excel and SPSS
> calculate percentiles and quartiles.
>
> As a simple demonstration, take the series of numbers from 1 to 10.
>
> Excel says that the 25th percentile is 3.25. SPSS says it is 2.75.
>

SPSS gives you 4 or 5 different choices for how to compute the
percentile.
a) Which are you using in SPSS?
b) Are you sure that none of them match the Excel standard?

--
Rich Ulrich, wpi...@pitt.edu
http://www.pitt.edu/~wpilib/index.html

[paulb...@my-deja.com]

In article <v3oq2t4rkees9c2lu...@4ax.com>,
wpi...@pitt.edu wrote regarding differences between Excel and SPSS:

>SPSS gives you 4 or 5 different choices for how to compute the
> percentile.
> a) Which are you using in SPSS?
> b) Are you sure that none of them match the Excel standard?

The method I was using in SPSS 10, which was the first one I thought
of, is Analyze/Descriptive Statistics/Frequencies, then click on the
button for Statistics and the check box for quartiles.

Here is the code that SPSS generates:

FREQUENCIES
VARIABLES=var00001 /FORMAT=NOTABLE
/NTILES= 4
/ORDER= ANALYSIS .

Or, alternatively, this yields the same result with my simple example
(which is what would be expected, of course):

FREQUENCIES
VARIABLES=var00001 /FORMAT=NOTABLE
/PERCENTILES= 25 50 75
/ORDER= ANALYSIS .

So, neither of these examples match what Excel comes up with (in Excel,
I was using the builtin functions for quartiles or percentiles. Like
SPSS, Excel is consistent within itself on this calculation.)

[Rich Ulrich]

On Tue, 05 Dec 2000 22:46:35 GMT, paulb...@my-deja.com wrote,
following up to my reply:

> In article <v3oq2t4rkees9c2lu...@4ax.com>,
> wpi...@pitt.edu wrote regarding differences between Excel and SPSS:
>
> >SPSS gives you 4 or 5 different choices for how to compute the
> > percentile.
> > a) Which are you using in SPSS?
> > b) Are you sure that none of them match the Excel standard?

pb>

> The method I was using in SPSS 10, which was the first one I thought
> of, is Analyze/Descriptive Statistics/Frequencies, then click on the
> button for Statistics and the check box for quartiles.
>
> Here is the code that SPSS generates:

[ snip. 2.75 and 8.25 result as quartiles for 10 digits, 1 ...10 ]

> So, neither of these examples match what Excel comes up with (in Excel,
> I was using the builtin functions for quartiles or percentiles. Like
> SPSS, Excel is consistent within itself on this calculation.)

- darned if I know what SPSS is doing. Or Excel. I can mention, I
can't get excited about difficulty in defining "25 percentile" for
small samples, where you don't have the definition falling on any
number at all. You can be absolutely firm in defining a "-tile" for
a number, by some textbook definition, where it seems like you need
another step of estimation when it comes to laying a "-tile"
description on top of a sample.

However, I see some sense in the Excel answer: if there were 9
scores, from 1 ... 9 , the naive answers < median of the
half-samples> should be 3 and 7, so you move it the bottom score up
by 0.25 with an extra digit on top. I don't know how you write
that as an algorithm.

When I wrote my earlier answer, what I had in mind was the SPSS
procedure called "RANK" -- and what it can do with providing
"percentile" information about the individual scores.

[Rich Ulrich]

Bruce Weaver just posted in sci.stat.edu on this topic.
He gives this URL where someone has a collected
comments on quartiles and (inconsistent) definitions.

http://exploringdata.cqu.edu.au/ticktack.htm

[Keith Mitchell]

A word of caution:
Do not use EXCEL for any type of Statistical Analysis. It's a brilliant
spreadsheet, and should only be used for that purpose.
Keith

paulb...@my-deja.com wrote:

> We've run across an apparent difference in the way that Excel and SPSS
> calculate percentiles and quartiles.
>
> As a simple demonstration, take the series of numbers from 1 to 10.
>
> Excel says that the 25th percentile is 3.25. SPSS says it is 2.75.
>

> At least they both agree that the 50th percentile is 5.5.
>
> For the 75th percentile, Excel says 7.75, and SPSS says 8.25.
>
> Is one of these more correct than the other? If so, why?
>
> Thanks.
>