Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: defining dynamic range to use in formulas

  1. #1
    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?
    Thank you for any help you can give me.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    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)

    adjust the range accordingly

    Hope that helps
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  3. #3
    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. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,337

    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. #5
    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. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,337

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Defining a dynamic range issue
    By JF in forum EXCEL HELP
    Replies: 4
    Last Post: June 8th, 2005, 01:01
  2. Formulas : dynamic named range for chart
    By DNAgirl in forum EXCEL HELP
    Replies: 2
    Last Post: April 8th, 2004, 20:07
  3. Formulas : dynamic named range
    By DNAgirl in forum EXCEL HELP
    Replies: 3
    Last Post: March 11th, 2004, 02:44
  4. Formulas: HLookup & Indirect & Dynamic Range
    By Pesky Weasel in forum EXCEL HELP
    Replies: 6
    Last Post: October 4th, 2003, 12:10
  5. Formulas: Dynamic named range
    By stoph in forum EXCEL HELP
    Replies: 2
    Last Post: September 13th, 2003, 01:23

Bookmarks

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