General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Excel help needed

Discussion in 'Trading Software' started by hans123, Aug 23, 2007.

1. ### 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?

offset

3. ### hans123

Thanks.

But what should be the complete command for the given example?

4. ### swandro

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.

Thanks.

6. ### Jerome

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.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: