need help regarding identifying certain cells

jasdes

New Member
Joined
Mar 31, 2002
Messages
5
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top