With the OFFSET function you can retrieve data from another location based on a starting address and incremental values moving up and down columns and/or left and right in rows. There are other options that allow the retrieval of multiple cells which in turn enables calculating sums and averages.
- [Voiceover] We're in the workbook called 12-Reference.…Excel has a number of formulas that are…referred to as reference formulas…and on the first sheet, it's called Offset,…that's one of the reference functions.…In this list here, we will be recording more data…in columns A and B.…Let's say every time we open this file,…we might eventually have some other data…off to the right, but we want to know…when the last reporting date was,…the latest closing rate,…the average of the last three days, five days,…possibly some other variations on that.…Let's talk about how we can get…data in place here and before we do,…I want to alert you to a function.…
You might have seen something…about this function in an earlier movie.…It's called Count A…and this function counts the number of…cells that have data.…For example, if I use this on column A,…it means how many cells have data there?…Ten of them.…We get the same answer for column B too.…There's another function called Count.…It counts the number of cells that have numbers in them.…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome46s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Get data from remote cells with OFFSET