Percentile: Different Result Using VBA?

J

Jezebel

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

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?
Click to expand...

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
 
P

(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?
 
P

(PeteCresswell)

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

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.
 
Top