Dear Friends,
I've am array formula that I use to provide highest values:
{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)), MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}
I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:
ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")
What I'm trying to achieve is to build a spreadsheet that would look up the 10 highest and ten lowest values within category (example). So first I want to the formula to consider only values in the category A and secondly only the values that are in column that is of interest. The attached example has only one - value but in practice there will be more. This is why the data range have to by dynamic. The numbers 2 and 128 have to reflect first and last row of the category that is of interest. The latter D have to reflect the column that is being considered.
P.S.
As this was quite important to me I posted the same post here
http://www.ozgrid.com/forum/showthread.php?t=167461
Bookmarks