Percentile: Different Result Using VBA?

If I populate some cells on an Excel spreadsheet with, say, 1, 2, 3, 4; and then
set another cell to =Percentile([cell range], .9) I get a result of 4.

OTOH, if I'm in VBA and feed the those numbers to
gExcelApp.WorksheetFunction.PercentRank, I get 3.7.

Only diff I can see is the use of a VBA array of double to pass the numbers 1-4.

Does this sound familiar to anybody?
--
PeteCresswell

Try formatting the cell containing the percentile function to display some
decimal places: then you'll get 3.7 as expected.



"(PeteCresswell)" <x@y.Invalid> wrote in message
news:o5f8m194nljt138uhfnc8pqip9t57rh9im@4ax.com...
> If I populate some cells on an Excel spreadsheet with, say, 1, 2, 3, 4;
> and then
> set another cell to =Percentile([cell range], .9) I get a result of 4.
>
> OTOH, if I'm in VBA and feed the those numbers to
> gExcelApp.WorksheetFunction.PercentRank, I get 3.7.
>
> Only diff I can see is the use of a VBA array of double to pass the
> numbers 1-4.
>
> Does this sound familiar to anybody?
> --
> PeteCresswell

On Sat, 29 Oct 2005 20:27:20 -0700, "(PeteCresswell)" <x@y.Invalid> wrote:

>If I populate some cells on an Excel spreadsheet with, say, 1, 2, 3, 4; and then
>set another cell to =Percentile([cell range], .9) I get a result of 4.
>
>OTOH, if I'm in VBA and feed the those numbers to
>gExcelApp.WorksheetFunction.PercentRank, I get 3.7.
>
>Only diff I can see is the use of a VBA array of double to pass the numbers 1-4.
>
>Does this sound familiar to anybody?

Not at all.

I get the same result of 3.7 either way.

What is the format of your result cell on your worksheet?


--ron

Per Jezebel:
>Try formatting the cell containing the percentile function to display some
>decimal places: then you'll get 3.7 as expected.

Good catch.

But I had it backwards. It was the VBA that was goofey. Turns out that when
I passed the K factor parm to my routine I was using a Long field instead of a
double. Therefore .9 became 1.... causing Excel.Percentile to return 4
instead of 3.7.

Thanks Jez, thanks Don.
--
PeteCresswell