Percentile function

[millzy7]

Hi

I am using the percentile function in vba but it is returning the wrong value.

The code:

Dim Array(1 to 2000) as variant
Dim myVar as double

myVar = Application.WorksheetFunction.Percentile(Array, 0.02)

Is there anything special about using this function with an array? Does the array need to be a double?

 

[TonyJollans]

Hi millzy7,

It ought to work. Can you give an example of a wrong result you are getting.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[millzy7]

Ya great,

These are the figures below. If i paste them into excel and calculate the percentile manually, the result is -17,606.
If i put all the figures in an array in vba and then pass the array into the Application.WorksheetFunction.Percentile() function, the result is -141,965. It must work differently when run in vba. Any help is appreciated.


8,787
-17,794
5,522
8,281
5,250
-9,292
-1,337
3,776
-2
2,205
10,304
-3,377
-355
-2
-18,327
709
-17,784
4,499
8,985
-16,911
8
1,000
-4,366
9,398
2,757
9,159
-1
10,057
7,201
-7,628
17,200
11,303
-10,321
-5,476
13,863
-10,375
-4,022
9,531
3,559
4,420
6,751
-5,754
5,284
8,329
1,126
-18,227
-13
-19,147
4,846
6,146
-10,472
11,485
-5,428
8,616
-9,208
-3,744
5,132
9,924
-7,351
6,288
-6,283
15,427
5,953
-3,199
-18,450
-1,462
-17,072
-3,229
-6,653
-1,374
-19,767
19,734
-9,556
3,203
763
3,984
11,288
-9,560
-17,947
3,500
-2,004
-892
1,317
9,132
-5,377
2,106
-16,468
-6,443
8,100
-12,651
6
1,262
-18,818
-6,440
5,630
9,076
-15,278
-2,625
720
488
-12,208
3,302
-6,450
3,549
6,759
-11,551
-10,005
1,306
2,265
-6,021
1,751
15,093
815
-2,484
-6,488
-1,055
8
-17,315
-1,124
2,646
-11,994
-2,654
-3,004
-12,405
-2,946
1,949
8,075
-7,081
-10,881
-754
6,871
-17,983
-660
1,160
9,958
-1,425
9,027
-1,642
5,692
-8,526
6,632
1,353
-6,206
-9,272
5,582
4
-15,242
-2,536
3,911
8,899
-7,995
-3,837
2,433
4,436
8,244
-9,387
9,584
-7,755
-366
4,684
-14,592
1,537
5,022
1,261
-2,111
7,443
-1,561
11,864
-800
-5,704
-2,643
-2,632
-8,061
4,183
-5,266
-116
-7,132
-1,098
1,467
-2,299
6,902
-475
-2,235
-313
-4,270
-3,890
15
-10,950
-3,136
-1,002
4,457
-8,141
5,643
8,444
-4,146
-1,590
2,675
-12,283
2,143
-10,714
2,055
8,893
-4,375
12,461
4,600
4,774
-7,388
7,105
-18,102
-1,459
-8,108
-3,468
-3,547
3,531
-3,694
469
-5,858
-3,411
1,850
-2,577
6,925
-4,193
-1,799
10,045
-2,470
3,395
-1,754
-13,055
-1,390
535
-3,427
-5,903
3,837
999
-4,387
3,236
4,137
382
-9,565
111
5,045
4,883
6,288
-6,442
6,066
-1,163
-13,582
-1,268
-3,256
6
-288
-9,475
2,450
1,041
-3,720
5,524
-5,864
5,802
997
-8,984
-2,402
3,700
-5,826
-1,362
-10,105
365
-3,091
-2,235
1,314
2
-1,261
-9,642
323
-1,411
2
2,568
-9,663
-1,404
-4,766
-5,898
6,510
-3,360
4,230
-5,069
-1,009
-5,153
3
657
-7,226
2,234
1,865
-9,097
7,080
9,496
-2,829
2,588
-2,169
289
6,043
-1,001
-9,975
1,509
-3,671
-8,907
3,285
3,896
6
-7,807
3,122
2,972
1,870
2,208
1,567
-3,323
-792
-370
-7,363
4,119
-1,267
-2,538
-1,290
5,839
4,322
9,905
10,654
-9,598
9,717
-3,605
-10,783
1,045
7,740
9,268
1,992
2,139
-13,435
747
-10,645
-4,729
520
-4,063
-6,707
-5,977
1,068
4,683
781
7
-3,936
9,335
1,539
-508
-4,341
-776
10,151
-4,089
-11,932
-430
2,845
-1,703
9,637
5,326
4,322
-7,300
-1,383
-1,745
4,744
9,177
7,622
-6,600
-1,244
570
584
7,434
-5,323
1,290
-154
-3,067
-1,256
-14,449
-1,636
-4,704
316
21
271
-2,810
5,216
-3,459
-14,136
-1,249
6,278
-3,296
10
6,801
-4,609
-1,013
55
-1,975
3,110
-3,418
-6,750
1,945
3,690
714
-753
-3,174
6,648
2,306
19
5,741
-1,216
5,809
-1,724
-964
-314
2,391
3,101
3,584
624
-5,550
8,904
-1,284
6,521
1,453
-8,519
-395
-3,792
-873
-3,404
4,066
718
10,718
10,917
-1,096
-11,243
1,932
8,105
-1,104
-11,459
-1,442
-9,949
2,660
-4,921
1,387
-558
-6,629
7
-1,831
5,289
-4,093
-1,294
-9,424
3,024
31
-5,388
2,964
-1,589
-4,088
-1,619
-2,329
4,618
-1,451
-3,466
4,049
-5,260
9,192
3,313
-1,188
4,436
-5,108
-3,476
-153
-13,768
-2,454
382
-4,821
6,729
5,197
-1,501
1,855
5,177
6,110
-3,450
-3,949
6,159
-237
-1,910
6,784
896
-12,453
-10,420
195
-2,934
142
-417
-9,843
-13,931
-3,042
831
947
-216
-7,316
-7,968
255
5,264
-4,314
-553
7,848
-4,579
325
-3,149
8
-557
2,550
3,348
-12,661
652
-593
46
7,652
-2,948
0

 

[TonyJollans]

Hi millzy,

I copied your numbers into column A (rows 1 to 520). I took your code and added a loop to fill the array from the cells ..

[blue]Sub test()
Dim Arrray(1 To 2000) As Variant
Dim myVar As Double
For Each cel In Range("a1:a520")
Arrray(cel.Row) = cel.Value
Next
myVar = Application.WorksheetFunction.Percentile(Arrray, 0.02)
MsgBox myVar
End Sub[/blue]

.. and got the result -17,605.78

So, I'm sorry, but I don't think I can help much more. Your answer is way below the minimum value so cannot be the result of a (properly working) percentile function but I don't know what you've done.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[millzy7]

Thanks Tony