Percentile: Different Result Using VBA?

[Jezebel]

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

 

[Ron Rosenfeld]

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

 

[(PeteCresswell)]

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)]

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.