+ Reply to Thread
Results 1 to 2 of 2

Using rank function in an array

  1. #1
    Registered User
    Join Date
    05-02-2011
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    15

    Using rank function in an array

    I need to rank groups of prices within a column separately which is easy enough except I don't know in advance how many prices are going to be in each group. ie the formula needs to be able to refer to a separate array which identifies the subsets within the column. I have got it working for calculating max, min, sum etc but I can't get the rank function to work.

    Eg the following calculates the smallest price within each of the groups. GR is the column with the prices and GW is the array that gives each subset of prices in that column a unique id. I copy this down the sheet and it picks out the smallest price for each of the subsets

    =MIN(INDIRECT("$gr$"&MAX($GW$2:$GW2)&":$gr"&ROW())))

    I am lost trying to create a formula to rank the prices within each subset. I was guessing something along the lines of

    =RANK(INDIRECT("$gr$"&MAX($GW$2:$GW2)&":$gr"&ROW())))

    but that came up with the error too few arguments. (I also need to add the 1 to make it sort ascending)

    Any ideas would be really appreciated as I find arrays very difficult and can't see how to fix this one
    Cheers
    Rhys
    Last edited by rhysspinner; 01-22-2015 at 09:15 AM.

  2. #2
    Registered User
    Join Date
    05-02-2011
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Using rank function in an array

    [solved]
    I found an old post and apparently you cant use an array within rank but the following worked like a charm
    =SUMPRODUCT(--(A$2:A$100=A2),--(C$2:C$100>C2))+1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Rank function using "array IF" formula as ref doesn't seem to work
    By vizzkid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 09:47 AM
  2. [SOLVED] Using the RANK function to rank decimal numbers
    By CRinne in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2013, 02:14 PM
  3. Rank With Array
    By John Vieren in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2012, 04:58 PM
  4. VBA Rank for an Array
    By PNCD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2009, 08:17 PM
  5. RANK function and Array Formula Error
    By Sparky12 in forum Excel General
    Replies: 4
    Last Post: 02-28-2009, 05:46 PM

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