How can the 'OFFSET' function be used for creating dynamic ranges in Excel?
Instruction: Provide an example of how the 'OFFSET' function can be utilized to reference a dynamic range of cells.
Context: This question assesses the candidate's knowledge of advanced Excel functions for dynamic data range selection, crucial for adaptive data analysis and modeling.
Updated
Official answer available
Read the opening below, then unlock the full answer and practical guidance.
OFFSET returns a reference displaced from a starting reference. Its optional height and width set the size; it does not move cells.
For a controlled example, put a header in A1 and a contiguous list of numeric amounts in A2:A101 with no intervening blanks or unrelated entries...