Excel OFFSET Function
Excel OFFSET function can help you to quickly duplicate a reference from a particular cell to any specified part. These parts could be in a row, column, a particular point or place in your spreadsheet.
You can also use the OFFSET function to calculate average values or sums on your spreadsheet. The list of things you could do with the OFFSET function is long.
=OFFSET (reference, rows, cols, [height], [width])
The OFFSET function could be a bit deceptive, so you need to follow the steps carefully to get the result you want.
reference: this is the beginning point that is needed in a cell as a reference.
rows: is directly below the reference.
cols: are the columns needed to be offset in relation to the reference. Other optional arguments are height and width.
In this article, we are going to use the OFFSET function in two ways; to get a total sum and the average sum in an excel spreadsheet.
We are going to be creating a data comprising the earnings of a generic organization over the year in its different locations. And this will be done for a specific quarter.
You can apply the same steps to any situation to get the sum.
Enter all data correctly, which includes the earnings for London, Bangkok, New York, and Thailand.
Figure 1. Excel showing the data entered correctly.
Specify the quarter for which the earnings would be generated.
Figure 2. Excel showing how the quarter is specified
Then enter the formula to get the total sum for that particular quarter.
Figure 3. Excel OFFSET function returns all the reference per cell.
Excel OFFSET Function Notes
- The OFFSET function can be problematic by making changes to the worksheet; these changes often result from other alterations in it.
- The REF! error is often returned when the offset value floats to the edge of your Excel spreadsheet.