+ Reply to Thread
Results 1 to 12 of 12

Cell Reference Problem with Offset and Large

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Cell Reference Problem with Offset and Large

    Hello everyone,



    In the Excel 2010 sheet attached, I have a tab called "Summary" with the formula: =LARGE(INDEX(('Tony Breau'!$V$3:$V$24=$B$5)*'Tony Breau'!$P$3:$P$24,0), 1), to solve for the largest account called within the week. $B$5 has the weeknumber of today(). And the formula refers to that. This function is also applied to "By # of Employees".

    My problem right now is getting the Account Name. In the "Tony Breau" tab, the revenue and total employees of some companies are identical, so when I do a lookup formula on these numbers, it always shows the first one of the list. I was wondering if I can refer to the cell that formula =LARGE(INDEX(('Tony Breau'!$V$3:$V$24=$B$5)*'Tony Breau'!$P$3:$P$24,0), 1) is referring to so I can offset it left a few units.

    Any help would be greatly appreciated! Thanks in advance.
    Attached Files Attached Files
    Last edited by zakkair; 06-25-2012 at 10:08 AM.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Cell Reference Problem with Offset and Large

    Hi Zakkair,

    Welcome to the forum.

    If I understand correctly, you need to have multiple output which matches the revenue 1.195.. something like below as a result in column K of Summary sheet. ?

    Prairie Pipeline Contractors Ltd
    Smartpipe Technologies Ltd
    Burke Builders Inc
    Mustang Trenching & Excavating Limited
    Summit Pipeline Services
    Ronalco Contracting Ltd


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Cell Reference Problem with Offset and Large

    Hi dilipandey,

    The real spread sheet has many different Revenue numbers, but some of the individual companies have the exact same revenue. The Summary sheet helps our sale representatives identify the most recent, most highly valued customers. So on the example, the top company called "this week" is K L M Contracting Ltd with a revenue of 1.19505. The formula LARGE(INDEX(('Tony Breau'!$V$3:$V$24=$B$5)*'Tony Breau'!$P$3:$P$24,0), 1) picks up the Revenue section with no problem.

    The formula solves for the largest Revenue within this week of data. So if K L M Contracting Ltd has a revenue of 0.01 but is called "this week", the summary sheet would display the 0.01 revenue. The problem I have right now is getting the company name. I used to use offset formula on the revenue (since most of the time, this value is unique). However, in certain weeks, where the value's not unique, such as the one shown. The offset function would always return the first 1.19508 it finds, which is "Prairie Pipeline Contractors Ltd" in this example. I want it to return "K L M Contracting Ltd"

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Cell Reference Problem with Offset and Large

    So on the example, the top company called "this week" is K L M Contracting Ltd with a revenue of 1.19505
    How you are judging that top company this week is "KLM contracting ltd" ? Is it the "Yes" in S16 in sheet Tony Breau?
    Also the revenue is 1.19508 not 1.9505 as you mentioned..


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Cell Reference Problem with Offset and Large

    Hi, its judging based on the weeknumber of the call date with today's week number (as to show the top 5 accounts from this week.

    The formula LARGE(INDEX(('Tony Breau'!$V$3:$V$24=$B$5)*'Tony Breau'!$P$3:$P$24,0), 1)

    It checks from V3:V24 to see if it equals B5 (the weeknumber of today()), and if it equals, it returns the list of values from P3:P24. Then the large function is applied to get the biggest of this list.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Cell Reference Problem with Offset and Large

    Ok... that formula gives the value 1.19508... which is revenue for almost all the companies..

    But I got the point.. you are picking the KLM contracting basis the point where B5 of summary tab is matching in column V of Toney Breau.. see the attached file :-

    Book1(21).xlsx


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  7. #7
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Cell Reference Problem with Offset and Large

    Hi,

    The problem that arises then is that if you match the weeknumber only, if for example, in the attached file, all four companies (of the same revenue) are called in the same week. Then the formula only shows the first company with the week number.
    Attached Files Attached Files

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cell Reference Problem with Offset and Large

    rank unique the revenue then lookup largest 5
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Cell Reference Problem with Offset and Large

    Hi Zakkair,

    Then the formula only shows the first company with the week number.
    That I got the hint already and replied to you in my post#2 that :-
    If I understand correctly, you need to have multiple output


    No worries...Martin has already given you that solution... thanks.


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  10. #10
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Cell Reference Problem with Offset and Large

    Thanks a lot Martin! It worked perfectly.

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cell Reference Problem with Offset and Large

    ok as requested by pm
    formula in p3 of tony breau is
    =IF(P3="","",IF(W3=Summary!$B$5,COUNT($P$3:$P$1000)-(RANK(P3,$P$3:$P$1000)+COUNTIF($P$3:P3,P3)-1)+1,""))
    dragged down
    the others are in summary sheet
    row 6 cols k l m
    =IF(1>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,1),'Tony Breau'!$Q:$Q,0),1))
    =IF(1>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,1),'Tony Breau'!$Q:$Q,0),11))
    =IF(1>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,1),'Tony Breau'!$Q:$Q,0),13))
    row 8 cols k l m
    =IF(2>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,2),'Tony Breau'!$Q:$Q,0),1))
    =IF(2>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,2),'Tony Breau'!$Q:$Q,0),11))
    =IF(2>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,2),'Tony Breau'!$Q:$Q,0),13))
    row 10 cols k l m
    =IF(3>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,3),'Tony Breau'!$Q:$Q,0),1))
    =IF(3>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,3),'Tony Breau'!$Q:$Q,0),11))
    =IF(3>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,3),'Tony Breau'!$Q:$Q,0),13))
    row 12 cols k l m
    =IF(4>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,4),'Tony Breau'!$Q:$Q,0),1))
    =IF(4>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,4),'Tony Breau'!$Q:$Q,0),11))
    =IF(4>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,4),'Tony Breau'!$Q:$Q,0),13))
    row 14 cols k l m
    =IF(5>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,5),'Tony Breau'!$Q:$Q,0),1))
    =IF(5>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,5),'Tony Breau'!$Q:$Q,0),11))
    =IF(5>COUNT('Tony Breau'!$Q:$Q),"",INDEX('Tony Breau'!$F:$R,MATCH(LARGE('Tony Breau'!$Q:$Q,5),'Tony Breau'!$Q:$Q,0),13))

  12. #12
    Registered User
    Join Date
    06-21-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Cell Reference Problem with Offset and Large

    Thank you so much. I didn't have access to the file at the workplace. This will save me a lot of time.

+ 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