Sometimes it is necessary to base a
calculation on a set of cells in different locations.
An
example would be when a total is required from certain months of the year, such
as the last 3 months in relation to the current date.
One
solution would be to retype the calculation each time new data is entered, but
this would be time consuming and open to human error.
A
better way is to indicate the start and end point of the range to be calculated
by using the =OFFSET() function.
The
=OFFSET() picks out a cell a certain number of cells away from another cell. By
giving the =OFFSET() the address of the first cell in the range which needs to be
totalled, we can then indicate how far away the end cell should be and the
=OFFSET()will give us the address of cell which will be the end of the range to
be totalled.
The =OFFSET() needs to know three
things;
1. A cell address to use as the
fixed point from where it should base the offset.
2. How many rows it should look up
or down from the starting point.
3. How many columns it should look left or right
from the starting point.
Using =OFFSET() Twice In A Formula
The following examples use =OFFSET() to pick
both the start and end of the range which needs to be totalled.
Example
The following table shows five
months of data.
To calculate the total of a specific
group of months the =OFFSET() function has been used.
The
Start and End dates entered in cells F71 and F72 are used as the offset to
produce a range which can be totalled.
Explanation
The following formula represent a
breakdown of what the =OFFSET function does.
The formula displayed below are only dummies, but
they will update as you enter dates into cells F71 and F72.
