+ Reply to Thread
Results 1 to 11 of 11

get cell reference from large formula?

  1. #1
    Registered User
    Join Date
    06-13-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    95

    get cell reference from large formula?

    Hi all,

    I want to list in col A the 20 top company in terms of a count i have in Col b.
    ColB has has a $ count and i know i can get teh top 20 values from teh column easily using =LARGE(B:B,1), =LARGE(B:B,2)..... =LARGE(B:B,20) in column A
    However, I do not want to return the VALUE of the count, i want to return the company, which is listed in Col D, so it is offset by 2 columns of the count.

    I was thinking something along the lines of = OFFSET( EQUATION , 0 , 2) where in EQUATION i can somehow get teh reference of the cell where I am getting the Large values from? Any idea how i can get the cell reference and not just the values themselves?

    =OFFSET( CELL("address",LARGE(B:B,1)) , 0 , 2) did not work

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: get cell reference from large formula?

    Why not just sort by the column of interest?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-16-2016
    Location
    Greece
    MS-Off Ver
    2013
    Posts
    69

    Re: get cell reference from large formula?

    Instead of using OFFSET try this:
    =INDEX(B1:B3,MATCH(LARGE(D1:D3,1),D1:D3,0),1)
    this example assumes that your company names are in the range B1:B3 and the values are in D1:D3, so this will give you the company with the largest amount, I'm sure you can figure out how to replicate for more companies...

    EDIT: of course yes you could just sort the data :P

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: get cell reference from large formula?

    Have you tried assigning index numbers to companies equaling the count?

    You could then use an array formula SMALL('assigned indexes',countif($A$2:A2,A2)), to return the unique companies even if they share a common count.

    Edit Of course sorting is simpler.
    Dave

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: get cell reference from large formula?

    Quote Originally Posted by nick93 View Post
    Instead of using OFFSET try this:
    That will work unless there is a tie.

  6. #6
    Registered User
    Join Date
    06-13-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    95

    Re: get cell reference from large formula?

    Yes that would make sense, wouldn't it? Alas, there is custom formatting on how the numbers need to appear, and the Count is derived from various vlookups referencing another workbook sheet. Filtering it is not doing anything or changing the order of the cells, so i dont mind having a snapshot of what companies from the large list i have that i need to focus on listed in Col A

  7. #7
    Registered User
    Join Date
    06-13-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    95

    Re: get cell reference from large formula?

    Hi Nick, that equation worked perfectly, thank you!

    I dont have much experience with the index, match equations so i try ot avoid them, but ultimately looks like those ones are the most useful in the end!

    Guess ill have to do some homework and so i can use them to my benefit.

    Thank you all!

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: get cell reference from large formula?

    Agreed, sorting column B in Descending order would list the top 20 right on top..

    But you could do this

    =INDEX(D:D,MATCH(LARGE(B:B,1),B:B,0))
    =INDEX(D:D,MATCH(LARGE(B:B,2),B:B,0))
    =INDEX(D:D,MATCH(LARGE(B:B,3),B:B,0))
    etc.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: get cell reference from large formula?

    vpan,

    What about duplicated counts?

  10. #10
    Registered User
    Join Date
    06-13-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    95

    Re: get cell reference from large formula?

    Hi FlameRetired - the numbers i have derived are so complex and large in millions/decimals I think there is a very small chance there is a duplicate. It is not a simple integer count, but thank you for thinking of any possible errors that i can run into, in case i ever have issues and do need to reference this again

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: get cell reference from large formula?

    You are welcome. Thank you for the feedback.

+ 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] cell reference from LARGE result
    By jwarburton3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2015, 01:28 PM
  2. Way to have number in cell reference a large conversion table?
    By jeffc19 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-21-2015, 10:52 AM
  3. Replies: 1
    Last Post: 02-11-2015, 01:56 PM
  4. [SOLVED] How to use LARGE to return a cell reference for use in OFFSET formula
    By TC1980 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-11-2013, 08:31 AM
  5. [SOLVED] Using cell value as worksheet name in formula reference(AVERAGE LARGE IF)
    By trizzo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-26-2013, 09:23 PM
  6. [SOLVED] Cell Reference Problem with Offset and Large
    By zakkair in forum Excel General
    Replies: 11
    Last Post: 06-25-2012, 12:06 PM
  7. Replies: 2
    Last Post: 03-07-2012, 03:16 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