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

 

[Michel Walsh]

Hi,


The XPercentile function returns a number in the sequence, while Excel
(seems to) do some kind of strange interpolate between existing data, as in
your example, 7.035 does not exist in the original data set.

You asked about:

..6667 of 11 samples = 7.333.

In position 7 you have a 0, in position 8 you have 10.5; XPercentile returns
the first existing value GREATER than the position 7.333, so it returns
10.5; as for Excel, I can't tell where, neither how, it got 7.035 (I would
have expect to return the value at x=7.3333 from a line passing by (x=7,
y=0) and (x=8, y=10.5), which is 3.5, not the value you reported, 7.035).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


I should have spoken of "rank" rather than position, as the 7th value is 0
and the 8th value is 10.5 (when the values are ordered in ascending order,
not necessary as they are presented, un-ordered).


Vanderghast, Access MVP