VBA: Dynamic Range in Percentile Formula

[upendra2206]

Hi,

I am trying to create a macro which will calculate percentile for a set of range in a row but the numbrs of cells change and hence the range needs to be dynamic. I have record the macro:

Sub mrexcel()


Range("A117").Select
ActiveCell.FormulaR1C1 = "=+PERCENTILE(R[-19]C[1]:R[-19]C[9],0.1)"
Range("A117").Select
Selection.AutoFill destination:=Range("A117:A125")
Range("A117:A125").Select

End Sub

Please note that the range here is from B98:J98. But it can be K98 or L98 or anything for that matter. I want the range to be dynamic. Please note that to select the data from B98:J98 I have used CTRL+SHIFT+RIGHT ARROW but the macro is fixing the cell address.

 

[oldbrewer]

4

4

5

6

6

7

7

8

4

6

7.3

assume your range always starts in E4

last number in your range is 8 in cell L1

I tweaked it so it does .1 .5 and .9

I thought 6 was wrong but of course

the gap is 6 minus 6

new macro

Dim nums(100)

For j = 5 To 100

summ = summ + 1

If Cells(1, j) = "" Then summ = summ - 1: where = summ + 5: GoTo 100

counter = counter + 1

nums(counter) = Cells(1, j)

Next j

100 gaps = summ - 1

For z = 1 To 9 Step 4

zz = z / 10

mynum1 = gaps * zz

dec = mynum1 - Int(mynum1)

lownum = nums(Int(mynum1) + 1)

highnum = nums(Int(mynum1) + 2)

wide = highnum - lownum

remndr = wide * dec

myanswer = lownum + remndr

Cells(1, where) = myanswer

where = where + 1

Next z

End Sub

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[upendra2206]

Hey got it.. Thanks alot.. Really appreciate the effort.

 

[oldbrewer]

happy to help - this 70 year old brain needs the work outs.........

 

[upendra2206]

Hehe.. Cheers !!