Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: need help regarding identifying certain cells

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello, what formula would i need to use in order to select a certain cell. For eg, if i had to find what day i spent the most money in, how would i identify the day in which it occurred? if the days were labelled in 1 col and the amount spent each day in the next col, what would i need to do in order to identify that certain day?

    thanx jason

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jason

    If your amounts are in Column "B" and days in Column "A" use:

    =INDEX($A$1:$B$1000,MATCH(MAX($B$1:$B$1000),$B$1:$B$1000,0),1)

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,006
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-01 22:30, jasdes wrote:
    hello, what formula would i need to use in order to select a certain cell. For eg, if i had to find what day i spent the most money in, how would i identify the day in which it occurred? if the days were labelled in 1 col and the amount spent each day in the next col, what would i need to do in order to identify that certain day?

    thanx jason
    Jason,

    Although a MAX value is a single number, it might occur more than once and you might want to know all dates where it occurs.

    Consider the following sample in A1:B6.

    {"Date","Amount";
    37257,40;
    37258,45;
    37259,60;
    37260,30;
    37261,60}

    The big numbers are dates as internally stored in Excel. Note that 60, which is the MAX amount, occurs at two different dates:

    37259 [ 3-Jan-02 ]
    37261 [ 5-Jan-02 ]

    If interested in getting both dates, there are two ways: one non-automatic and one formula-based which is fully automatic.

    A non-automatic method is using Advanced Filter.

    Insert 3 rows before the data. This operation shifts the data area to A4:B9, including labels. The actual data start then in row 5.

    In A1 enter: MaxDate

    In A2 enter: =B5=MAX(B:B)

    Activate A5.
    Activate Data|Filter|Advanced Filter.
    Check Copy to another location.
    Accept List range if $A$4:$B$9, otherwise edit it.
    Enter $A$1:$A$2 as value of Criteria range.
    Enter $D$1 as value of Copy to.
    Click OK.

    A formula-based method to get all dates with which the MAX amount is associated.

    The sample is in A1:B6.

    Activate the option Insert|Name|Define.
    Enter Endrow as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Log!$A:$A)

    [ Note. Change the "Log" bit to the name of the sheet where your data is. ]

    Click OK.

    In C1 enter: Max [ just a label ]

    In C2 enter: =MAX(B1:INDIRECT("B"&EndRow))

    In D2 enter and copy down to a few rows:

    =IF(ISNUMBER(E2),MATCH(E2,$A$1:INDIRECT("A"&EndRow),0),"")

    In E1 enter: MaxDate [ just a label ]

    In E2 enter:

    =INDEX($A$1:INDIRECT("A"&EndRow),MATCH(C2,$B$1:INDIRECT("B"&EndRow),0))

    Format E2 as date.
    This delivers the first date where the MAX amount occurs.

    In E3 enter:

    =IF(COUNTIF($B$1:INDIRECT("B"&EndRow),$C$2)>COUNT($E$2:E2),INDEX($A$1:INDIRECT("A"&EndRow),MATCH($C$2,INDIRECT("B"&D2+1):INDIRECT("B"&EndRow),0)+D2),"")

    and format E3 as date then drag it down until no more dates are produced.

    Aladin

    P.S. Other, alternative, set-ups for the last method can be found at http://www.mrexcel.com/wwwboard/archive.shtml

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •