+ Reply to Thread
Results 1 to 3 of 3

picking same SMALL and LARGE values from list

  1. #1
    Registered User
    Join Date
    04-27-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Thumbs up picking same SMALL and LARGE values from list

    Hello,

    I am struggling on creating correct formula to display top 3 and bottom 3 results and additional info relating to result in same row but different column.
    I have tried using VLOOKUP or MATCH formulas to combine with LARGE/SMALL and OFFSET, but I'm not quite there yet.
    I get the right SMALL/LARGE score to display and with OFFSET to show correct name, but there is a problem when I have same SMALL/LARGE result more than once. So I searched the forums and changed it a bit adding ROW formula and it worked almost. Now I get error when I have only one SMALL/LARGE value.

    I attached copy of workbook and highlighted formula fields I'm struggling with. Yellow with LARGE results using array formula with ROW function I found on forum and adopted (probably wrong because I'm not 100% understood) and Green with my original choice using MATCH function.

    Basically my aim is to display top 3 and bottom 3 team names according on their weekly performance increase an decrease. These results are displayed in Departmental sheet.

    Thank you in advance and hoping to find solution for this problem without using macros if possible. If not I'll take advice for VBA also.
    Attached Files Attached Files
    Last edited by Karolis; 06-27-2011 at 11:23 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: picking same SMALL and LARGE values from list

    In E10 enter:
    Please Login or Register  to view this content.
    in E12
    Please Login or Register  to view this content.
    in E14:

    Please Login or Register  to view this content.
    in E19:

    Please Login or Register  to view this content.
    in E21:
    Please Login or Register  to view this content.
    in E23:
    Please Login or Register  to view this content.

    in C10 enter:

    Please Login or Register  to view this content.

    confirmed with CTRL+SHIFT+ENTER
    not just ENTER... then copy that cell and paste to C12, C14, C19, C21, C23
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-27-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Thumbs up Re: picking same SMALL and LARGE values from list

    You are a GENIUS!
    COUNTIF and INDEX works nice together, I'll keep this in my mind for future.

    Thank you VERY MUCH for solving this and making my formula shorter and doing EXACTLY what I wanted.

+ 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