Designating a cell in a formula by using a formula

**mandre** · 10-01-2007, 12:33 PM

Hello,

I am trying to designate a cell in a formula by using a formula. I want to calculate the first quartile, median, and third quartile of the data in a column, but below the actual data are dummy "0"s that I cannot get rid of (I need them for other purposes). I want to set the array for the quartile to be the last actual data entry. I have the number of data records (in cell A1), and as the data start on row 2, the array of actual data (not including dummy "0"s which will skew the quartile calculations) may be defined as "D2:D(A1+1)" where A1 is the number of data records. Thus for the first quartile the formula would look something like "=quartile(D2:D(A1+1), 1)" . However, clearly this is not the correct way to do this as Excel balks at my attempt.

Is this possible to do?

**Mark@Work** · 10-01-2007, 12:55 PM

one way would be to use the OFFSET() function:
=QUARTILE(OFFSET(D2,0,0,A1+1),1)
another would use the INDIRECT function:
=QUARTILE(INDIRECT("D2:D"&(A1+1)),1)
Mark.