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	7	6	3	5	4	7	99
			YOU CAN NAME A RANGE "MYRANGE" AND DEFINE IT AS
			=OFFSET(Sheet3!$B$1,0,0,1,COUNTA(Sheet3!$B$1:$R$1))
			the range will now be B1 to the last data value (in this case 99)
			I have made the last possible value R1
			but you can set it to anything
			135		the 135 is =sum(myrange)

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

 

[upendra2206]

Hi, Thanks for a quick reply. The problem with this solution is that I have to apply this formula in the first empty cell after the last filled data. Here since you have named the B1:R1 as MyRange, if I were to apply the formula in any of the cell between B1:R1 it gives m a circular reference.

So if my last filled data cell was say M1 then how can I make the macro work to calculate the percentile in N1.

 

[oldbrewer]

				4	7	6	3	5	4	7	99	67.5
					assume your range always starts in E4
					and for demo purposes the percentile formula is all the numbers added up
					and divided by 2
					last number in your range is 99 in cell L1
					range total = 135
					half of that is 67.5
					this macro puts the 67.5 into cell M1
					For j = 5 To 100
					If Cells(1, j) = "" Then firstblank = j: GoTo 100
					Sum = Sum + Cells(1, j)
					Next j
					100 Cells(1, firstblank) = Sum / 2
					End Sub

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

 

[upendra2206]

Hi, Not much of a coder. Can u please help me with the whole code. Really appreciate your help and sorry for the inconvenience.

 

[oldbrewer]

the last but one line is where you put YOUR formula - tell me what it is and I will do it

 

[upendra2206]

Hi, Sorry for a delayed response.

For:

	E	F	G	H	I	J	K	L	M	N	0
4	4	7	6	3	5	4	7	99	10th Percentile	50th Percentile	90th Percentile

<tbody>
</tbody>

M4 = 10th percentile i.e. =Percentile(MyRange,0.1)
N4 = 50th percentile i.e. =Percentile(MyRange,0.5)
O4 = 90th Percentile i.e. =Percentile(MyRange,0.5)

Thanks in Adavance

 

[oldbrewer]

the numbers MUST be in ascending order

4

4

5

6

6

7

7

8

5.1

assume your range always starts in E4

last number in your range is 8 in cell L1

5.100

this macro calculates the third percentile and puts it in cell M1

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

pctile = 0.3

mynum1 = gaps * pctile

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

End Sub

<tbody>
</tbody>

 

[upendra2206]

Hi, but what about the 50th and 90th percentile?

 

[oldbrewer]

you can see the logic in the macro - can you not do 2 more calcuilations and put the answer in cells(where+1) and where+2 ?