# Thread: making a formula with a variable range (Excel xp)

1. ## 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

Rob

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

4. ## 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.

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)

#### Posting Permissions

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