OFFSET- Other Examples

Please purchase the course before starting the lesson.

Exercise 5

In this example, for ease of input the blue cells are inputs and they are captured in months (along the row- D8 to O8) and years (down the column- C9 to C14).

Our model however needs all the numbers in a single row.

In the yellow cells, using the OFFSET and the rows 19 and 20 create a formula that looks in the correct cell for the relevant information

Exercise 6

In this example we want to easily extract every 17th cell starting at C9 (orange cells) into AD9 column.

Put your calculations in the yellow cells.

TIP: In the first yellow column, create a counter starting from 0 in increments of 17

In the second yellow column, use OFFSET to refer to every 17th cell from the first orange cell. Use the counter cells you have created in AC9 column.

Exercise 7

In this example you want to compare the selling price of a house to the prime interest rate at the time.

However, you realise that the prime rate may not affect the house price immediately. Perhaps there is a delayed effect.

The orange cell (C9) contains the correlation between the Prime rates and house prices.

We want to create a formula in C446 down that will move the prime rate backwards or forwards depending on what we enter in cell C10. For example if we put a 1 in the cell, we want to see the prime rate 1 month later.

By playing with the number in C10, determine which lag appears to be the best correlation (the closer to 100% the better the match)

To see the full course contents click here.

 

Back to: Advanced Excel Course > Key Lookup Functions