Subscriber only lesson.
Sign up to this course to view this lesson.
OFFSET Part 3
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
- SUM(OFFSET(E20,,,,-E19)) is saying:
- Start at cell E20
- Don’t move any rows
- Don’t move any columns
- Ignore the height
- The width will come from the number in cell E19. The (-) in front of cell E19 instructs excel to move to the left
- It then sums cell E20, plus the number of cells to the left of E20, specified by cell E19
- In this example only one cell will be added which = 25
Lesson notes are only available for subscribers.