Index, Match, Min and Max question

I'm trying to retrieve a date (in column A) that corresponds with a
Min and Max amount (in columns B:D). Basically I need to know when my
amounts hit their Highs and Lows. Can someone help me with this
please?

Thanks,
Maria

0
11/7/2007 7:41:16 PM
excel 39879 articles. 2 followers. Follow

5 Replies
607 Views

Similar Articles

[PageSpeed] 48

Dates in column A; values in B
Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0))
Returns the date corresponding the max value in B
Is this what was needed?
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<mgriffiths@klmtel.net> wrote in message 
news:1194464476.732636.234500@50g2000hsm.googlegroups.com...
> I'm trying to retrieve a date (in column A) that corresponds with a
> Min and Max amount (in columns B:D). Basically I need to know when my
> amounts hit their Highs and Lows. Can someone help me with this
> please?
>
> Thanks,
> Maria
> 


0
bliengme5824 (3040)
11/7/2007 8:30:18 PM
Kind of... I need it to return the date corresponding to the max value
in B, C or D and cannot seem to come up with the formula to look at
all three columns.

I appreciate the help!

On Nov 7, 2:30 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Dates in column A; values in B
> Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0))
> Returns the date corresponding the max value in B
> Is this what was needed?
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPwww.stfx.ca/people/bliengme
> remove caps from email
>
> <mgriffi...@klmtel.net> wrote in message
>
> news:1194464476.732636.234500@50g2000hsm.googlegroups.com...
>
>
>
> > I'm trying to retrieve a date (in column A) that corresponds with a
> > Min and Max amount (in columns B:D). Basically I need to know when my
> > amounts hit their Highs and Lows. Can someone help me with this
> > please?
>
> > Thanks,
> > Maria- Hide quoted text -
>
> - Show quoted text -


0
11/7/2007 8:45:46 PM
Try these array formulas**:

For the MAX:

=INDEX(A1:A10,MAX((B1:D10=MAX(B1:D10))*ROW(B1:D10)-MIN(ROW(B1:D10))+1))

Format the cell as DATE

Note that if there are duplicate MAX values the formula will return the date 
for the *LAST* instance of the max value.

For the MIN:

=INDEX(A1:A10,MIN(IF(B1:D10=MIN(B1:D10),ROW(B1:D10)-MIN(ROW(B1:D10))+1)))

Format the cell as DATE

Note that if there are duplicate MIN values the formula will return the date 
for the *FIRST* instance of the min value.

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER)

-- 
Biff
Microsoft Excel MVP


<mgriffiths@klmtel.net> wrote in message 
news:1194464476.732636.234500@50g2000hsm.googlegroups.com...
> I'm trying to retrieve a date (in column A) that corresponds with a
> Min and Max amount (in columns B:D). Basically I need to know when my
> amounts hit their Highs and Lows. Can someone help me with this
> please?
>
> Thanks,
> Maria
> 


0
biffinpitt (3172)
11/7/2007 9:54:10 PM
Thanks!!! It works great, and I'd have never thought of doing it that
way!

On Nov 7, 3:54 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try these array formulas**:
>
> For the MAX:
>
> =INDEX(A1:A10,MAX((B1:D10=MAX(B1:D10))*ROW(B1:D10)-MIN(ROW(B1:D10))+1))
>
> Format the cell as DATE
>
> Note that if there are duplicate MAX values the formula will return the date
> for the *LAST* instance of the max value.
>
> For the MIN:
>
> =INDEX(A1:A10,MIN(IF(B1:D10=MIN(B1:D10),ROW(B1:D10)-MIN(ROW(B1:D10))+1)))
>
> Format the cell as DATE
>
> Note that if there are duplicate MIN values the formula will return the date
> for the *FIRST* instance of the min value.
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
> --
> Biff
> Microsoft Excel MVP
>
> <mgriffi...@klmtel.net> wrote in message
>
> news:1194464476.732636.234500@50g2000hsm.googlegroups.com...
>
>
>
> > I'm trying to retrieve a date (in column A) that corresponds with a
> > Min and Max amount (in columns B:D). Basically I need to know when my
> > amounts hit their Highs and Lows. Can someone help me with this
> > please?
>
> > Thanks,
> > Maria- Hide quoted text -
>
> - Show quoted text -


0
11/7/2007 10:19:44 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


<mgriffiths@klmtel.net> wrote in message 
news:1194473984.621433.175580@v3g2000hsg.googlegroups.com...
> Thanks!!! It works great, and I'd have never thought of doing it that
> way!
>
> On Nov 7, 3:54 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>> Try these array formulas**:
>>
>> For the MAX:
>>
>> =INDEX(A1:A10,MAX((B1:D10=MAX(B1:D10))*ROW(B1:D10)-MIN(ROW(B1:D10))+1))
>>
>> Format the cell as DATE
>>
>> Note that if there are duplicate MAX values the formula will return the 
>> date
>> for the *LAST* instance of the max value.
>>
>> For the MIN:
>>
>> =INDEX(A1:A10,MIN(IF(B1:D10=MIN(B1:D10),ROW(B1:D10)-MIN(ROW(B1:D10))+1)))
>>
>> Format the cell as DATE
>>
>> Note that if there are duplicate MIN values the formula will return the 
>> date
>> for the *FIRST* instance of the min value.
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>> <mgriffi...@klmtel.net> wrote in message
>>
>> news:1194464476.732636.234500@50g2000hsm.googlegroups.com...
>>
>>
>>
>> > I'm trying to retrieve a date (in column A) that corresponds with a
>> > Min and Max amount (in columns B:D). Basically I need to know when my
>> > amounts hit their Highs and Lows. Can someone help me with this
>> > please?
>>
>> > Thanks,
>> > Maria- Hide quoted text -
>>
>> - Show quoted text -
>
> 


0
biffinpitt (3172)
11/7/2007 10:24:54 PM
Reply:

Similar Artilces:

Open Relay Question.
How do I disable open relay on exchange server 2000? Relaying is disabled by default, but if you want to check it, follow the instructions in this article. http://www.petri.co.il/preventing_exchange_2000_2003_from_relaying.htm Tony www.activedir.org "Jason" wrote: > How do I disable open relay on exchange server 2000? ...

SQL Server Question
I know that this is probably the wrong place to put this, but I don't know where the SQL server communities are. Therefore, I am hoping someone can help me anyway. I am very new to SQL server, but have extensive experience in Access. I am now moving an Access database to SQL Server. One of my tables has about 34000 records. I moved this to SQL and then added a new field called bit_Supply. Now I cannot modify any of the values of the table in Access. Here is the problem. For some reason, Access says the data in the field is "0" (zero). SQL Server says the data in t...

Trouble matching overdue payments
This seems to be a constant problem that I would like to find a fix for. Money (2005 s.b.) somehow doesn't match payments from my credit card to reoccurring bills. It then shows that I have an overdue bill even though I paid it. Is there some way to match the overdue bill to a payment I already made? It seems like my only options are "skip occurrence" (which I always do) or "enter into register". Both options suck. Anybody have any ideas? Thanks. ...

What is the max length of a single piece of code?
I am trying to make this rather long piece of code work, but when I paste it, the VBA window makes it Red 'error' Text. Is their a maxiumum length that VBA will allow? stAppName = "C:\Program Files\Internet Explorer\iexplore.exe http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.RunWaypoint_1 + ", London" & (" to: " + Me.RunWaypoint_2 + ", London") & (" to: " + Me.RunWaypoint_3 + ", London") & (" to: " + Me.RunWaypoint_4 + ", London") & (" to: ...

To index or not to index
I have a table of stuff stored in a repository and an attached table of inventory dates, linked one-to-many by an Autonumber ID field. I regularly need to find the oldest or newest inventory dates (or all, in order by date) for each item record in the inventory table, which is normally an automatic case for indexing. But this stuff is not inventoried very often, so far, only two out of over 80,000 records have three records in the inventory table, all others have zero, one or two inventory records. This is NOT going to change. It will likely be decades before there are as many as...

a few questions about outlook xp
I have a few questions for the outlook expert outthere... :P 1) Is there a way with outlook xp so that instead of receiving a message in the main inbox such as: "Your message did not reach some or all of the intended recipients." (for example with a relay denied) a window pops up telling about it, and the message stays in the outbox instead of disappearing from there? (it is important when using IMAP accounts and sending email) I wish the email would stay in the "outbox" folder until it's really sent. (like with most email clients) 2) How can I force outlook to alway...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

INDEX/MATCH help
I just learned about the INDEX/MATCH function while searching some of the Excel tip pages - and I think I can make good use of it. What I want to do is this: worksheet 1 - is the format of our Income Statement worksheet 2 (titled TB) is our Trial Balance. I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount for each account #. I tested this out and my formula is working fine. However, sometimes an account # on worksheet 1 doesn't appear on Worksheet 2 because there wasn't any activity. In this case I get a $N/A. I need to edit my formula so that if there ...

Thanks. Another e-mail question
Thanks to Brian and Wes for their responses to my question about e-mailing to a large list. I have a follow-up question. I see how you can click on the "To" or "Cc" button and it brings up a window with fields for "To", "Cc", and "Bcc" to enter recipient names into. It seems, though, that the only way to get names into the "Bcc" field is to click on whatever names I have entered into the Contact Database. I cannot just paste a name into the "Bcc" field. This is a slight problem, because I have been maintaining m...

Create Clustered index or Covering Index
Which would be better on a 3 column temporary table? A Clustered index or a Covering Index? In this case the uniqueidentifier is always the same and is done this way because there are about 20 procedures that already use this as a static table and I don't want to impace all the procedures with this change. CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE CLUSTERED INDEX idx on #temp (CID) or CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE INDEX idx on #temp (CID) INCLUDE (PID,a) Thanks, Tom Tom, Better for what? It depends....

Chart
Hello, I created a line column chart on 2 axis. I have four data sets. The first 3 should go on the primary Y axis as columns and the 4th should go on the secondary Y axis as a line. However, Excel wants to automatically put both the 3rd and 4th data sets on the secondary axis as lines. I cannot find any command to change the 3rd data set to go onto the primary axis as a column. Any suggestions would be most appreciated! -Patty On Tue, 11 Nov 2003 13:27:33 -0800, Patty = <anonymous@discussions.microsoft.com> wrote: > Hello, > > I created a line column chart on 2 ...

Email question
I use Outlook Express for sending & receiving email. After downloading IE7, the ability to underline words in email seems not to be available any more along with the font color I used to write emails. What happened and can I get these features back? Tools | Options | Send. Is the Mail Setting Format set to Plain Text or HTML? You won't even see the Format Bar if Plain Text is selected. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "jag" <yagerj@charter.net> wrote in message news:lXj8n.31026$BV.25131@newsfe07.iad... >...

Question to check the conditions and then print the result.
Hey guys can anyone help me? my question is that ... Assume that there is a student work sheet and there are 4 columns ........ now we have to check that if the marks of the first two columns(checking individually) are less than 40 and also the marks of the next two columns are less than 30(checking individually). if these two conditions satisfy then i should get the grade as pass in the grade column. hope u got my question. =IF(AND(C2<40,D2<40,E2<30,F2<30),"Pass","") -- __________________________________ HTH Bob "Neelakanta" <Neelakanta@di...

Stupid question
I'm currently using Microsoft Money 2005 and they are continuously trying to sell me 2006. So why am I still using office 2003? (and One Note 2003) When is an update that really improves on some of these Outlook probs & expands some of the valued features expected??? Money and Outlook have nothing to do with each other. What are you really asking? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, spondee asked: | I'm...

mailbox permission question
Is there an easy way in A.D. / Exchange 2003 to grant mailbox read permissions for an entire store to a given user / admin? thanks! Scott, you can do this by right-clicking the Administrative group which contains the information store you want access to, and delegate Full Exchange permissions to the user / admin you want. Or, you can right-click the Exchange server > security > Assign appropriate permissions Hope this helps. Regards, Mark Hewitson MCSE+M mhewitson[at]infomedia[dot]com[dot[au] "scott mcfadden" wrote: > Is there an easy way in A.D. / Exchange 20...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Auto Accept Agent question.
Hi, I have successfully deployed Auto Accept Agent and it looks like its working fine. The only issue I have is that once the meeting is accepted, the schedule in the free/busy does not get updated. It only updates if I log into the resource mailbox. Is there another event sink or agent that would update the free/busy schedule once the meeting has been accepted? Thanks, Sam Jaffer. ...

Match & Index??
I have the following info in different workbooks. In workbook 1, I have in Columns A,B,C,D: Mark & No. Start End Deal SLGG1234 3/15/2004 6/15/2004 211 SLGG1234 1/1/2004 3/14/2004 111 SLGG1234 6/16/2004 8/15/2004 311 SLGG1255 2/13/2004 8/15/2004 411 In workbook 2, I have Columns A,B, C: Mark & No. Date Deal SLGG1234 3/14/2004 SLGG1234 6/14/2004 In Column C of workbook 2, I want a formula to look at Cols A and B. Compare the info in them to Cols A,B and C in workboo...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...

text and picture colour matching
Hi there I am producing a document which has a picture and text. I am trying to make them the same colour. I set the text and the picture to the same rgb colour numbers 255 (i.e. royal blue). They look the same on the screen and yet when they print out the picture and the text are very different shades of blue. It's an issue I've had before in pub 2003 but didn't in pub 2000 on the same printer! Any suggestiions greatfully received. Thanks Ken Are you running the most current printer driver? -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <anonymous@discussio...

Under HELP, where is the INDEX
In Word 2003, is the INDEX completely gone? I always used it to search topics and definitions of terms. Hi MS decided to change the help system :-( So the Index feature is gone. Maybe you have your old Office CDs still available and can copy the old help files -- Regards Frank Kabel Frankfurt, Germany "Perotin" <Perotin@discussions.microsoft.com> schrieb im Newsbeitrag news:34B1A278-5835-48DD-9A74-1E0E78441485@microsoft.com... > In Word 2003, is the INDEX completely gone? I always used it to search topics > and definitions of terms. ...

System Restore question.
I have WindowsXP SP3 Where does the System Restore program reside? I want to add it to the programs to be left alone in CCleaner. Would I have to add some registry entry too? Desperate, Frank "Frank Martin" <fm@general.com.au> wrote in message news:Or8c58B3KHA.3568@TK2MSFTNGP04.phx.gbl... > I have WindowsXP SP3 > > Where does the System Restore program reside? I want to add it to the > programs to be left alone in CCleaner. > > Would I have to add some registry entry too? > > Desperate, Frank If you're despera...

Why do my data labels do not match source data?
I have created a chart but my data labels do not match the source data. The labels state, Series 1, Series 2, etc. Hi, If the labels read series 1, series 2 etc it would suggest that you have not specified a range in which to pick up the series name from. Right click the chart and pick Source Data... On the Series tab check the Name control contents. Cheers Andy KeithRD48 wrote: > I have created a chart but my data labels do not match the source data. The > labels state, Series 1, Series 2, etc. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Simple pie chart question
I'm a teacher trying to make a pie chart illustrating a part of a whole as a percent. I have two columns, one column (A1-A10)contains the total possible points for assignments 1 -10 the other column (B1- B10)contains the points earned for assgnments 1-10. the cell below each column has a formula that displays the sum of cells X1 - X10. So if a student has earned 75 points out of a possibe 100 you will see 100 in cell A11 and 75 in cell B11. OK. My question is how do I make a pie chart that will display something that looks like it is 75% one color and 25% another. I keep getting ...

XmlTextReader Question #4
Hi, I am parsing a xml string for a specific element using XmlTextReader (code below). I now need to change it to get the element's value plus get the parent node's name. Can someone help me? Thanks public object GetResponseValue(string xml, string element) { using (XmlTextReader reader = new XmlTextReader(new System.IO.StringReader(xml))) { object elementValue = null; object elementTag = reader.NameTable.Add(element); while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element) { if (reader.Name.Equals(elementTag)) { elementValue = reader.ReadElementContentAsObject(); brea...