XPercentile function returns different value to Excel

[TomP]

Hi There,

Any help would be greatly appreciated from this forum:

I am using the XPercentile function available here:

http://www.mvps.org/access/queries/qry0019.htm

The problem is that it sometimes it returns different values to that of

the Percentile function in Excel (when using exactly the same array of
data). For example, using the following array "Outlet_Size:"

Outlet_Size
0
0
0
0
0
0
15
0
13.5
15.75
10.5

In Access, Xpercentile("Outlet_Size","PercentileBaseArray",0.667)
returns 10.5
In Excel, =PERCENTILE($A$2:$A$12,0.667) returns 7.035

Can anyone shed any light as to why the different values? Does the
Excel function work in a different way? Ultimately, my results in
Access need to be consistently the same as in Excel.

Many thanks in advance for any help.

Tom

 

[Tim Ferguson]

Outlet_Size
0
0
0
0
0
0
0
10.5
13.5
15
15.75

In Access, Xpercentile("Outlet_Size","PercentileBaseArray",0.667)
returns 10.5
In Excel, =PERCENTILE($A$2:$A$12,0.667) returns 7.035

Can anyone shed any light as to why the different values?

One is interpolating, the other one is taking the nearest data point. I
don't think that either one is wrong or right -- in this case, it's
simply a weakness of using percentiles (or, in this case you've specified
three sf, so it's actually a mill-ile): in some statistical circles it
would be called dishonest.

Does the Excel function work in a different way?

Evidently. What question do you want to answer:

1) if these data were representative of a whole population of something,
how big an outlet size would be big enough for the smallest two-thirds?

2) out of these data, how big an outlet is big enough for the smallest
two-thirds?

When you know which is the right question, you'll be able to get the
correct solution!

Ultimately, my results in
Access need to be consistently the same as in Excel.

Don't they need rather to be correct?

HTH


Tim F