Announcement

Collapse
No announcement yet.

defining dynamic range to use in formulas

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #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)

    adjust the range accordingly

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

    Comment


    • #3
      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.

      Comment


      • #4
        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
        Kris

        ExcelFox

        Comment


        • #5
          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.

          Comment


          • #6
            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
            Kris

            ExcelFox

            Comment

            Working...
            X