Compose a Range reference from Address() function

[rxp409]

Hi I might be going about this the wrong way but here goes.

I have a column of dates in A as the first column in a larger table of data. I have a summary of the data in a separate table which feeds into a chart. For example =PERCENTILE(D16:D6855,0.9). I want to set a start and end date by entering these dates in other cells. So when i change the start date cell D16 then changes to the relevant row in column D.

Assume this start date is entered in a cell named start, and the column of dates has been renamed dates. I have got as far as =ADDRESS(MATCH(start, dates,1),4) which returns the relevant cell ... but as a text string.

So when i enter =PERCENTILE(ADDRESS(MATCH(start, dates,1),4):D6855,0.9) it has a problem. My question is

1. How do i translate this text output from ADDRESS() into something which i can use as a ref in percentile.
2. When i've done that, to select a range with the end date can i still just place : between them?
ADDRESS(start...) : ADDRESS (end...)

If this has been asked and answered before please redirect, as i haven't been able to find anything, nor even know what to search for! Any help much appreciated.

 

[mikerickson]

I think the function that you are looking for is INDIRECT.
as in PERCENTILE(INDIRECT(ADDRESS(MATCH(start, dates,1),4) & ":D6855"),0.9)

There is one problem with INDIRECT, it is volatile, i.e. every time any cell is changed the formula recalculates. This can slow a worksheet. You could use a non-volatile formulation

=PERCENTILE(INDEX($D:$D, MATCH(start, dates,1) :INDEX($D:$D, MATCH(end, dates,1), 1), 0.9)

 

[rxp409]

Literally just got it, thanks very much.