+ Reply to Thread
Results 1 to 2 of 2

Simplifying RANK,MATCH, INDEX Formula

  1. #1
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Simplifying RANK,MATCH, INDEX Formula

    Attached is an example of a much more complex spreadsheet where I want to find the two most frequent numbers in a column and set a conditional format to highlight anything outside their boundries. Ideally, I'd like the formula to be self contained in the conditional formatting window but I don't think this is possible. I had to create a table of 4 columns to come up with my boundries

    Column 1 lists the numbers using the MAX function (my numbers always exhibit themselves as powers of 2 and wouldn't exceed 6 levels)

    Column 2 has a countif to get the frequency

    Column 3 Ranks them in such a way that there are no duplicates even if the frequency is the same

    Column 4 uses INDEX and MATCh to trace back to the numbers with the most frequency and place them in cells that the Conditional Formatting will reference.

    Is there an easier way?

    Thanks

    ChemistB
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Well not with standing that you may have ties, in which case it fails, the conditional format needs to be

    =AND(ISNUMBER(B2),OR(B2>max($G$2:$G$3),B2<min($G$2:$G$3))


    As the is no guarentee which way round the values ranked first and second occur. But this does not answer your question!


    Regards

    Dav
    Last edited by Dav; 07-06-2007 at 10:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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