Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    making a formula with a variable range (Excel xp)

    I have attached a wkbk with a sheet called total. The top rows have formulas accessing the rows beneath it, rows 25 to 104.

    I am writing a macro to cut and paste rows from another wkbook into this one starting at row 25. This time it encompasses rows 25 to 104 or n=80. Next time however, it may be n=85 or greater. How do i make the formulas in the top rows calculate a variable range of cells like this? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    thank you
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a formula with a variable range (Excel xp)

    Your attachment is corrupted. Edit your post, re-attch and re-submit .

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a formula with a variable range (Excel xp)

    You could use a formula like the one below for cell C4:

    <pre>=PERCENTILE(OFFSET(C25,0,0,COUNTA(C25:C500),1 ),0.1)
    </pre>


    This formula assumes that the data will never extend below cell C500.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: making a formula with a variable range (Excel xp)

    =PERCENTILE(C25:INDEX(C:C,MATCH(BigNum,C:C),1),0.1 )

    The C25:INDEX(C:C,MATCH(BigNum,C:C),1) bit specifies the dynamic range you need. It's a non-volatile specification.

    The BigNum is a defined name...

    (1.) Activate Insert|Name|Define.
    (2.) Enter BigNum as name in the Names in Workbook book.
    (3.) Enter the following in the Refers to box:

    9.99999999999999E+307

    (4.) Click OK.

    Aladin

    Postscript: You can even put

    =MATCH(BigNum,C:C)

    in e.g., A3 and refer to this cell in all formulas like...

    =PERCENTILE(C25:INDEX(C:C,$A$3,1),0.1)
    Microsoft MVP - Excel

Posting Permissions

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