Help with Excel for Mac 2011 - Functions

[VillageGirl]

Hello all, and thank you for whatever help you can provide.

I am using excel to look at historical financial data. I have used a query to pull and update historical stock data from Yahoo Finance. I need to lookup the cell with the lowest value, return that value and the date of that value to another part of the spreadsheet. I am using the following function successfully to find the cell address for the 90 day low.

=CELL("address",OFFSET(A1,MATCH(MIN(A1:A10),A1:A10,0)-1,0))​

I need to then
1. Have the value of the cell in another cell on the spreadsheet and
2. Have the value of the cell 3 columns to the left (Containing the date) in another cell.

The function above returns an absolute cell address, but I seem unable to manipulate this further to accomplish 1 and 2. Moreover, I need both 1 and 2 to recalculate whenever I refresh the data, so no hardcoding is possible.

I have googled extensively and don't even know how to phrase the question to get started. Any assistance to point me in the right direction would be most appreciated!

 

[Perpa]

Hello all, and thank you for whatever help you can provide.

I am using excel to look at historical financial data. I have used a query to pull and update historical stock data from Yahoo Finance. I need to lookup the cell with the lowest value, return that value and the date of that value to another part of the spreadsheet. I am using the following function successfully to find the cell address for the 90 day low.

=CELL("address",OFFSET(A1,MATCH(MIN(A1:A10),A1:A10,0)-1,0))​

I need to then
1. Have the value of the cell in another cell on the spreadsheet and
2. Have the value of the cell 3 columns to the left (Containing the date) in another cell.

The function above returns an absolute cell address, but I seem unable to manipulate this further to accomplish 1 and 2. Moreover, I need both 1 and 2 to recalculate whenever I refresh the data, so no hardcoding is possible.

I have googled extensively and don't even know how to phrase the question to get started. Any assistance to point me in the right direction would be most appreciated!

VillageGirl,
Welcome to the Boards.
I am using a PC with Windows 7 and Excel 2007...but since you are looking for a formula solution, you might see if the following formulas will work for you.

From what you described it appears that your data is formatted similarly to this:

Excel 2007
	A	B	C	D	E	F
1	Date					ID
2				150
3				140
4				130
5				120
6				110
7				100
8				90
9				80
10				70
11	6/14/2016			60		Test1
12				50
13				40
14				30
Sheet1

Your formula gives the address of the MIN value of the range in question. For demonstration I considered the range A1:F11. I adjusted your formula which became:

=CELL("address",OFFSET(D2,MATCH(MIN(D2:D11),D2:D11,0)-1,0))

To get the value (MIN value) I used the following formula in cell S8:

=MIN(IF(D2:D11<>0,D2:D11,FALSE))

Then to "Have the value of the cell in another cell on the spreadsheet ..." I used the following formula in cell S9:

=+DGET(A1:F11,"ID",S1:S8)

And finally, to "Have the value of the cell 3 columns to the left (Containing the date)..." I used the following formula in cell S10 and formatted the cell as a date, '6/14/2016':

=+DGET(A1:F11,"Date",S1:S8)

You can move your equations wherever you need to, but you must include the range from row 1 (if that is where your headers are) to the row where the MIN value is located in that column. So you could use H1:H2 in place of S1:S8 in the DGET formulas. You just have to include all the cells from the header row down to the cell with the MIN value in your range.
Give these formulae a try. Hopefully they are supported in Mac 2011.
Good luck.
Perpa

 

[VillageGirl]

Hi Perpa!

Thank you so much for your help and suggestions. I did manage to resolve all of my issues - not using your functions, but I think working through your suggestions really helped me to clarify what I needed and how to go about it.

So, here is what I did:

Example Chart

Date	High	Low	Close
14-Jun	3,028.21	3,028.21	3,028.21
13-Jun	3,130.43	3,130.43	3,130.43
12-Jun	3,121.29	3,121.29	3,121.29
11-Jun	3,117.62	3,117.62	3,117.62
10-Jun	3,151.69	3,151.69	3,151.69
9-Jun	3,142.52	3,142.52	3,142.52
8-Jun	3,112.99	3,112.99	3,112.99
7-Jun	3,064.03	3,064.03	3,064.03
6-Jun	3,054.34	3,054.34	3,054.34
5-Jun	3,060.86	3,060.86	3,060.86
4-Jun	3,039.19	3,039.19	3,039.19
3-Jun	2,942.09	2,942.09	2,942.09
2-Jun	2,924.23	2,924.23	2,924.23
1-Jun	2,911.98	2,911.98	2,911.98
31-May	2,871.57	2,871.57	2,871.57
30-May	2,909.36	2,909.36	2,909.36
29-May	2,890.35	2,890.35	2,890.35
28-May	2,962.28	2,962.28	2,962.28
27-May	2,953.28	2,953.28	2,953.28
26-May	3,004.93	3,004.93	3,004.93
25-May	3,044.10	3,044.10	3,044.10
24-May	3,004.87	3,004.87	3,004.87
23-May	2,986.73	2,986.73	2,986.73
22-May	3,042.42	3,042.42	3,042.42
21-May	3,051.23	3,051.23	3,051.23
20-May	3,043.10	3,043.10	3,043.10
19-May	3,062.05	3,062.05	3,062.05
18-May	3,067.21	3,067.21	3,067.21
17-May	3,091.98	3,091.98	3,091.98
30 Day Low		15
31-May		2,871.57

<tbody>
</tbody>

So I used the Min function [=MIN(C2:C30)] to find the 30 day low point value.

Then I used the MATCH function [=MATCH(C34,C2:C30,0)] to give me the row number of the cell containing the 30 day low. In the example, this is the 15.

Finally, I used the Index function [=INDEX(A2:A30,C33,1)] to give me the date of the low. This function found the entry in the Date column in the same row as the 30 day low.

So thank you for your help. I hope this helps someone else too!

VillageGirl

 

[Perpa]

Hi Perpa!



So, here is what I did:Thank you so much for your help and suggestions. I did manage to resolve all of my issues - not using your functions, but I think working through your suggestions really helped me to clarify what I needed and how to go about it.

Example Chart

Date	High	Low	Close
14-Jun	3,028.21	3,028.21	3,028.21
13-Jun	3,130.43	3,130.43	3,130.43
12-Jun	3,121.29	3,121.29	3,121.29
11-Jun	3,117.62	3,117.62	3,117.62
10-Jun	3,151.69	3,151.69	3,151.69
9-Jun	3,142.52	3,142.52	3,142.52
8-Jun	3,112.99	3,112.99	3,112.99
7-Jun	3,064.03	3,064.03	3,064.03
6-Jun	3,054.34	3,054.34	3,054.34
5-Jun	3,060.86	3,060.86	3,060.86
4-Jun	3,039.19	3,039.19	3,039.19
3-Jun	2,942.09	2,942.09	2,942.09
2-Jun	2,924.23	2,924.23	2,924.23
1-Jun	2,911.98	2,911.98	2,911.98
31-May	2,871.57	2,871.57	2,871.57
30-May	2,909.36	2,909.36	2,909.36
29-May	2,890.35	2,890.35	2,890.35
28-May	2,962.28	2,962.28	2,962.28
27-May	2,953.28	2,953.28	2,953.28
26-May	3,004.93	3,004.93	3,004.93
25-May	3,044.10	3,044.10	3,044.10
24-May	3,004.87	3,004.87	3,004.87
23-May	2,986.73	2,986.73	2,986.73
22-May	3,042.42	3,042.42	3,042.42
21-May	3,051.23	3,051.23	3,051.23
20-May	3,043.10	3,043.10	3,043.10
19-May	3,062.05	3,062.05	3,062.05
18-May	3,067.21	3,067.21	3,067.21
17-May	3,091.98	3,091.98	3,091.98
30 Day Low		15
31-May		2,871.57

<tbody>
</tbody>

So I used the Min function [=MIN(C2:C30)] to find the 30 day low point value.

Then I used the MATCH function [=MATCH(C34,C2:C30,0)] to give me the row number of the cell containing the 30 day low. In the example, this is the 15.

Finally, I used the Index function [=INDEX(A2:A30,C33,1)] to give me the date of the low. This function found the entry in the Date column in the same row as the 30 day low.

So thank you for your help. I hope this helps someone else too!

VillageGirl

VillageGirl,
I am glad you found your formulae, although what I offered couldn't have been much help now that I see your data.
Sometimes we over complicate things... Seeing what you wound up with makes much more sense than where we were going at the start.
It really helps to see the starting data. Nice job.
Perpa