Results 1 to 3 of 3

Dynamic range inside array formula with use of match

Threaded View

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    EU
    MS-Off Ver
    Excel 2007
    Posts
    17

    Angry Dynamic range inside array formula with use of match

    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
    Attached Files Attached Files
    Last edited by konradz; 07-11-2012 at 04:04 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1