making a formula with a variable range (Excel xp)

Topic

New Reply

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?

thank you

Reply | Quote

Replies

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

Rob

Reply | Quote

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

=PERCENTILE(OFFSET(C25,0,0,COUNTA(C25:C500),1),0.1)

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

Reply | Quote

=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)

Reply | Quote