Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

OFFSET returns a cell or range offset from another

Use OFFSET to return a cell or range offset a specified number of rows and columns from a given reference point. The first argument, specifies the reference point. The second and third arguments specify the number of rows and columns to move from the reference point; in other words, where the new range starts. The OFFSET function has the following syntax:

OFFSET(reference; rows; columns)
OFFSET(reference; rows; columns; height)
OFFSET(reference; rows; columns; height; width)
Image:Tip.png If the width or height is included, the OFFSET function returns a range. If both the width and height are missing, a cell reference is returned.

If the height or width are missing, they default to 1. If the height is present, then a range reference is returned rather than a cell reference. Using values from Table 1, Listing 10 uses OFFSET to obtain the quiz scores for the student named Bob.

Listing 10. Complex example of OFFSET.

=SUM(OFFSET(INDIRECT(ADDRESS(MATCH("Bob";A1:A16; 0); 4)); 0; 0; 1; 2))

In its entirety, Listing 10 is complex and difficult to understand. Table 12 isolates each function in Listing 10, providing an easy to understand explanation of how the example works.

Table 12. Breakdown of Listing 10.

Function Description
MATCH("Bob";A1:A16; 0) Returns 4 because Bob is the fourth entry in column A.
ADDRESS(4; 4) Returns $D$4.
INDIRECT("$D$4") Converts $D$4 into a reference to the cell D4.
OFFSET($D$4; 0; 0; 1; 2) Returns the range D4:E4.
SUM(D4:E4) Returns the sum of Bob’s quiz scores.

Although Listing 10 works as intended, it breaks easily and unexpectedly. Consider, for example, what happens if the range is changed to A2:A16. MATCH returns an offset into the provided range, so MATCH("Bob";A2:A16 ; 0) returns 3 rather than 4. ADDRESS(3; 4) returns $D$3 rather than $D$4 and Betty’s quiz scores are returned instead of Bob’s. Listing 11 uses a slightly different method to obtain Bob’s quiz scores.

Listing 11. Better use of OFFSET.

=SUM(OFFSET(A1; MATCH("Bob"; A1:A16; 0)-1; 3; 1; 2))

Table 13 contains a description of each function used in Listing 11. To help convince yourself that Listing 11 is better than Listing 10, replace A1 with A2 in both Listing 11 and Table 13 and notice that you still obtains Bob’s quiz scores.

Table 13. Breakdown of Listing 11.

Function Description
MATCH("Bob";A1:A16; 0)-1 Returns 3 because Bob is the fourth entry in column A.
OFFSET(A1; 3; 3; 1; 2) Returns the range D4:E4.
SUM(D4:E4) Returns the sum of Bob’s quiz scores.
Image:Tip.png The first argument to OFFSET can be a range so you can use a defined range name.

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Creative Commons License Design by Interspire