From the course: Excel 2016: Advanced Formulas and Functions

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Getting data from remote cells with the OFFSET function

Getting data from remote cells with the OFFSET function

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Getting data from remote cells with the OFFSET function

“

- In this worksheet called OFFSET, we've got some reporting dates in Column A, some closing rates, perhaps stock market quotes, in Column B. The list will grow. As we open this worksheet at different times, we always want to see up top the latest reporting date and the latest closing rate. We might eventually want to see the average of the last three days, the last five days, and maybe some more. We could be using the index function here, to come up with these results here. But a function called OFFSET will work even better. And will allow us, unlike index, to calculate these entries as well. So, before doing this, we will also point out a function called COUNTA, which you might be familiar with. We will use this, along with the OFFSET function. COUNTA simply counts the number of cells in a range that are not blank. So if we're looking at Column A, of course COUNTA can look anywhere, we see nine entries. Nine cells have data. Or there are nine non-blank cells. We'll use that…

Contents