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.
=SUM(E24:OFFSET(E24,0,0))
This example uses E24 as the starting point and offsets no rows or columns which results in the range being summed as E24:E24.
This example uses E29 as the starting point and offsets 1 col to pick
out cell F29 resulting in a the range E29:F29 being summed.
=SUM(E34:OFFSET(E34,0,2))
This example uses E34 as the starting point and offsets 2 cols to pick out cell G34 resulting in a the range E34:G34 being summed.
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.
=SUM(OFFSET(E45,0,1):OFFSET(E45,0,1))
The cell E45 has been used as the starting point for both offsets and each has been offset by just 1 column. The result is that just cell F45 is used as the range F45:F45 for the sum function to calculate.
=SUM(OFFSET(E51,0,1):OFFSET(E51,0,2))
The cell E51 has been used as the starting point of both offsets, the first offset is offset by 1 column, the second by 2 columns. The result is the range F51:G51 which is then totalled.
=SUM(OFFSET(E57,0,1):OFFSET(E57,0,3))
The cell E57 has been used as the starting point for both offsets, the first offset is offset by 1 column, the second by 3 columns. The result is the range F57:H57 which is then 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.
=SUM(OFFSET(D79,0,MONTH(F71)):OFFSET(D79,0,MONTH(F72)))
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.
Formula 1 =SUM( OFFSET(D79,0,MONTH(F71)) : OFFSET(D79,0,MONTH(F72)) )
This is the actual formula entered by the user.
Formula 2 =SUM( OFFSET(D79,0,MONTH(2)) : OFFSET(D79,0,MONTH(3)) )
This shows how the =MONTH function calculates the month number.
In this example the values of the months are 2 and 3 for Feb and Mar.
These values are the 'offsets' relative to cell D79.
Formula 3 =SUM( OFFSET(D79,0,2) : OFFSET(D79,0,3) )
This shows where the month numbers are used in the =OFFSET function.
Formula 4 =SUM( F79:G79 )
This shows how the =OFFSET eventually equates to cell addresses to be used as a range for the =SUM function.
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.
C

D

E

F

G

H

I


23

Total

Jan

Feb

Mar

Apr

May


24

10

10

400

500

600

700

This example uses E24 as the starting point and offsets no rows or columns which results in the range being summed as E24:E24.
C

D

E

F

G

H

I


29

410

10

400

500

600

700

C

D

E

F

G

H

I


34

910

10

400

500

600

700

This example uses E34 as the starting point and offsets 2 cols to pick out cell G34 resulting in a the range E34:G34 being summed.
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.
C

D

E

F

G

H

I


44

Total

Jan

Feb

Mar

Apr

May


45

400

10

400

500

600

700

The cell E45 has been used as the starting point for both offsets and each has been offset by just 1 column. The result is that just cell F45 is used as the range F45:F45 for the sum function to calculate.
C

D

E

F

G

H

I


51

900

10

400

500

600

700

The cell E51 has been used as the starting point of both offsets, the first offset is offset by 1 column, the second by 2 columns. The result is the range F51:G51 which is then totalled.
C

D

E

F

G

H

I


57

1500

10

400

500

600

700

The cell E57 has been used as the starting point for both offsets, the first offset is offset by 1 column, the second by 3 columns. The result is the range F57:H57 which is then 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.
Type
in the Start month.

Feb98

Type
in the End month.

Mar98

C

D

E

F

G

H

I


74

Total

Jan98

Feb98

Mar98

Apr98

May98


75

900

10

400

500

600

700


77

1020

15

20

1000

2000

3000


79

13

5

3

10

800

900

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.
Formula 1 =SUM( OFFSET(D79,0,MONTH(F71)) : OFFSET(D79,0,MONTH(F72)) )
This is the actual formula entered by the user.
Formula 2 =SUM( OFFSET(D79,0,MONTH(2)) : OFFSET(D79,0,MONTH(3)) )
This shows how the =MONTH function calculates the month number.
In this example the values of the months are 2 and 3 for Feb and Mar.
These values are the 'offsets' relative to cell D79.
Formula 3 =SUM( OFFSET(D79,0,2) : OFFSET(D79,0,3) )
This shows where the month numbers are used in the =OFFSET function.
Formula 4 =SUM( F79:G79 )
This shows how the =OFFSET eventually equates to cell addresses to be used as a range for the =SUM function.
No comments:
Post a Comment