Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: defining dynamic range to use in formulas

1. I agreed to these rules
Join Date
19th March 2005
Posts
14

## defining dynamic range to use in formulas

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?

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: defining dynamic range to use in formulas

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)

Hope that helps

3. I agreed to these rules
Join Date
19th March 2005
Posts
14

## Re: defining dynamic range to use in formulas

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.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,476

## Re: defining dynamic range to use in formulas

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

5. I agreed to these rules
Join Date
19th March 2005
Posts
14

## Re: defining dynamic range to use in formulas

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.

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,476

## Re: defining dynamic range to use in formulas

Hi,

Try this,

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

HTH

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno