Index max match function

[raidinkar]

Hi,
Will somebody guide for Max match function, basically the query is for finding the date when the stock hit its highest from the open, high, low, closing prices of a stock for various dates. The first column is represented by date the second one is represented by open prices, the third is represented by days high prices and the fourth is represented by low and the fifth is represented by closing prices.
Regards, Dinkar

 

[alow]

You should be able to use the following (with some adjustment for where your actual data is) to get the desired results:

=INDIRECT("A"&MATCH(MAX(B1:B10),B1:B10,0))

The assumptions here are that you have your dates in Column A (The A in quotes) and that your first set of data is in column B. You want to make sure to leave the first number in the 2 rangers (B1:B10) as a 1 and change the 2nd number in the ranges to the last row of your stock data. This should give you what you want. Good Luck!

 

[raidinkar]

Its not working, the data points are as below:

Date Open Price High Price Low Price Close Price
1/1/2013 73.75 74.7 71.5 71.7
1/2/2013 75 75.65 73 73.75
1/3/2013 75.05 76.7 74.1 75.05
1/4/2013 78 80.4 74.65 75.3
1/5/2013 77.85 78.3 74.4 74.8
1/6/2013 73.7 79.45 73.7 76.65
1/7/2013 76.45 76.8 75.45 75.95
1/8/2013 73.95 79.5 73.85 75.2

Query: The max of all the prices is 80.4, so how to find the corresponding date to the high price (date 1/4/2013)

Regards, Dinkar

 

[alow]

I see what you are looking for now. I thought you were trying to find the MAX value in each column and return a date, not the highest value overall. Finding the max and matching a row to it over a 2D grid is much different than find the location in a column. If this is a stock project, and you have a column that already gives the daily HIGH value, you could simply apply the formula I posted previously to the HIGH column to get what you need.

 

[raidinkar]

I am not getting it, i think i may not have understood. Can you give the formula, i repeat the query is what is the date when the stock made the high in a given period.

 

[alow]

use the formula I had posted above, just adjust your ranges to look at the column that has the High Prices listed in it. If you're not sure how to do that, I will need to know what columns (lettered) have which data and what rows have column headings and what rows have data.

 

[raidinkar]

Hi,

The Rows are ABCDEF and col are 1 to 10. The price data (Open price, High Price, Low Price, Closing Price) given is for a stock XYZ for a range of dates wherein i need to find the date when the stock made it Highest, Lowest.



A B C D E F
1 Date Open High Low Close Total Volume
Price Price Price Price

2 29/11/2013 69 72.7 68.5 70.65 563503
3 28/11/2013 68 68.65 67.8 68.25 68247
4 27/11/2013 68.9 68.9 67.6 67.75 49313
5 26/11/2013 70.45 70.45 68.3 68.45 60596
6 25/11/2013 70.5 70.9 69.4 69.8 78826
7 22/11/2013 70.9 71.2 69.8 70.05 77822
8 21/11/2013 71.5 71.5 69.55 69.9 73778
9 20/11/2013 71 72.35 70.7 71.1 86285
10 19/11/2013 70.05 72.75 70.05 71.95 168429

I think this would be very clear now. Thanks, Dinkar

 

[alow]

For the high use:

=INDIRECT("A"&MATCH(MAX(C1:C10),C1:C10,0))

For the low use:

=INDIRECT("A"&MATCH(MIN(D1:D10),D1:D10,0))

That should do it for you.

 

[raidinkar]

Sorry, The issue is the max number can be from multiple col i.e either from B, C, D.
I am trying to find the date corresponding to the highest price from the data given in all the columns and rows. I hope this time we will get the ans.
Regards, Dinkar

 

[alow]

If you're talking about stock prices, the highest price should only be in the High column, and the lowest in the Low column. If you insist on getting a 2 dimensional lookup for the max values, I'll need some patience because those formulas are much different.

 

[raidinkar]

Yes you are right its a two dimensional look up.Thanks for the help, i ll wait for your reply.