Excel help needed

H

hans123

I have a spreadsheet in Excel with columns like Date, Open, High, Low and Close. The next column has entries with positive integers. If the entry is X, I want to display the Open of X-days ago in the next column.

For example, the first row contains column headers and cell B6 contains the integer "4", than I want to display the Open of cell F2 in cell B7.

Can someone help me?

Thanks in advance.
 
Quote from hans123:

I have a spreadsheet in Excel with columns like Date, Open, High, Low and Close. The next column has entries with positive integers. If the entry is X, I want to display the Open of X-days ago in the next column.

For example, the first row contains column headers and cell B6 contains the integer "4", than I want to display the Open of cell F2 in cell B7.

Can someone help me?

Thanks in advance.
More...

offset
 
Here is an example. If this formula was in cell D7

=IF(C7=60,OFFSET(D7,-4,-2),"")

Then it is saying: If the value in C7 is equal to 60, place in D7 the value identified by the offset, otherwise put a blank in D7.

What does the offset point to? It takes D7, moves -4 rows up (a positive number would move down), moves -2 columns to the left (a positive number would move to the right).

So from D7, 4 rows up and 2 columns to the left would give you the value in B3.

If you copy this formula down a column, it will always give you the value relative to the cell containing the formula.
 
Can also just do this...

=IF(C7=60,B3,"")

B3 (is 4 rows up and 2 rows to the left). So you can use absolute (B3) or relative (OFFSET,etc.) values in formulas.
 
You must log in or register to reply here.
Back
Top