defining dynamic range to use in formulas

[snap203]

My question is: After I define a range in VBA code, how can i use it in formulas such as the percentile formula? I did a search on the forums to find this code:
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("B1:B" & LastRow)

Basically, I paste data in column A, which drive a formula in column B. Column B fills in based on data in column A and fills in 0's for the rest. The above code allowed me to select the non-zero range. Now i want to find the percentile of that range in cell C1. HOw do I do that?
Thank you for any help you can give me.

[xlite]

hi snap203,

Welcome to Ozgrid.com

if you only need a formula in C1 that counts the percentage of
non zero values in a range in column B, then try:

=(COUNT(B1:B20)-COUNTIF(B1:B20,0))/COUNT(B1:B20)

adjust the range accordingly

Hope that helps

[snap203]

Thanks for replying. Actually, i don't want percentage. i'm looking for percentile, which uses the formula =percentile(array,k), where k is something like .9 or .95. i defined the array in VBA code, but the k percentile is designated in the spreadsheet. How can i make a cell in the spreadsheet say, "=percentile(array designated in VBA code, D1)." The array is the part that's messed up. when i try to name the array in VBA code, it doesn't recognize it in the spreadsheet. Thanks for any help you can offer.

[Krishnakumar]

Hi,

You could define the dynamic range by formula too.

Hit Ctrl+F3 and type the following formula in the refers to box and define a name.

=OFFSET(Sheet1!$B$1,0,0,MATCH(1E+307,Sheet1!$A:$A))

change the sheet name with yours.

HTH

[snap203]

Thank you so much! The formula =OFFSET(Sheet1!$B$1,0,0,MATCH(1E+307,Sheet1!$A:$A)) worked. Only slight issue, but the formula still works. The 1st row is blank and when i use your formula, the formula counts the first row. It doesn't affect the result,but i was wondering if there was a way not to include the first row. I thought of changing B1 to B2, but then it makes the range start from B2 and goes down one cell too far.

[Krishnakumar]

Hi,

Try this,

=OFFSET(Sheet1!$B$2,0,0,MATCH(1E+307,Sheet1!$A:$A)-1)

HTH