OFFSET for dynamic range selection

OFFSET helps you build formulas that adjust as a list grows, which is useful in dashboards and reports that always need the latest records.

In this exercise, you'll use OFFSET to define a range that covers only the most recent entries in a sales log.

How OFFSET works

=OFFSET(reference, rows, cols, height, width)

• reference: the starting cell or range
• rows: how many rows to move from the starting point
• cols: how many columns to move left or right
• height: how many rows tall the returned range should be
• width: how many columns wide the returned range should be

OFFSET returns a reference, not just a single value. That means you can place it inside functions like SUM or AVERAGE to calculate over a dynamic section of data.

Your task

The sales log is in A1:C13. Cell F1 contains the value for "last N entries", which is currently 5.

The answer area is to the right of the log so the list can keep growing downward. For the entry count, use the whole Date column and subtract the header row.

1. In F2, calculate the total number of sales entries in the log.
2. In F3, calculate the sum of the last N values from the Amount column using SUM with OFFSET.
3. In F4, calculate the average of those same last N values using AVERAGE with OFFSET.

Your formulas should update correctly if the value in F1 changes or new sales rows are added below the current log.