Thanks:  0
Likes:  0

# Thread: need help regarding identifying certain cells

1. 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. 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. 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.
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.

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

## User Tag List

#### Posting Permissions

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