Subscriber only lesson.
Sign up to this course to view this lesson.
OFFSET Part 2
Discover how to use OFFSET function in a formula.
When to use
In its most basic form, OFFSET(Ref,x,y) will select a reference x rows down (-x would be x rows up) and y rows to the right (-y would be y rows to the left) of the reference Ref.
- The syntax for OFFSET is as follows: OFFSET(Reference,Rows,Columns,[Height],[Width])
- The arguments in square brackets (Height and Width) can be omitted from the formula (they both have a default value of 1)
- The above example will include Height and Width in the OFFSET formula. The SUM formula will be used to sum the cells
- OFFSET(B4,2,3,2,2) would give us an error, so it is necessary to include an aggregate function before the OFFSET function. Here the SUM function will be used. Therefore, SUM(OFFSET(B4,2,3,2,2) = 78
- This formula is summing the array of cells E6:F7
Lesson notes are only available for subscribers.