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.
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.