need help regarding identifying certain cells

[jasdes]

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

[Dave Hawley]

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)

[Aladin Akyurek]

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