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