Difference between revisions of "Manuals/calci/OFFSET"
| (7 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | =OFFSET( | + | <div style="font-size:30px">'''OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width)'''</div><br/> |
where, | where, | ||
| − | *<math> | + | *<math>ReferenceRange</math> is a reference cell or base cell of the offset, |
| − | *<math> | + | *<math>RowsOffset</math> represents the number of cells up or down the reference cell, |
| − | *<math> | + | *<math>ColumnsOffset</math> represents the number of cells left or right to the reference cell, |
*<math>Height</math> is an optional value that represents the number of rows to be displayed as the output, and | *<math>Height</math> is an optional value that represents the number of rows to be displayed as the output, and | ||
*<math>Width</math> is an optional value that represents the number of columns to be displayed as the output. | *<math>Width</math> is an optional value that represents the number of columns to be displayed as the output. | ||
| − | + | **OFFSET(), returns a reference offset from a given reference. | |
| − | OFFSET() | ||
== Description == | == Description == | ||
| − | OFFSET( | + | OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width) |
| − | *OFFSET function is used display the value of cell that is specified number | + | *OFFSET function is used to display the value of cell that is specified number of rows or columns away from the reference. |
| − | *Offset reference should be within the spreadsheet, else Calci displays | + | *Offset reference should be within the spreadsheet, else Calci displays #NULL error message. |
| − | *<math> | + | *<math>RowsOffset</math> can be positive or negative. If <math>Rows</math> is positive, it means move down from the reference. If <math>RowsOffset</math> is negative, it means move up from the reference. |
| − | *<math> | + | *<math>ColumnsOffset</math> can be positive or negative. If <math>Columns</math> is positive, it means move right from the reference. If <math>ColumnsOffset</math> is negative, it means move left from the reference. |
| − | *If <math>Height</math> or <math>Width</math> is omitted, Calci assumes it to be the same Height | + | *If <math>Height</math> or <math>Width</math> is omitted, Calci assumes it to be the same Height or Width as the reference. |
| − | *<math>Height</math> and <math>Width</math> should be > 1, else Calci displays # | + | *<math>Height</math> and <math>Width</math> should be > 1, else Calci displays #NULL error message. |
== Examples == | == Examples == | ||
| Line 54: | Line 53: | ||
|} | |} | ||
| − | =OFFSET(A2,2,2,1,1) : Returns the value in cell that is located two rows down (2) and one row to the right (2) from A2. Displays '''18''' as the output. | + | =OFFSET(A2,2,2,1,1) : Returns the value in cell that is located two rows down(2) and <br />one row to the right(2) from A2. Displays '''18''' as the output. |
| − | =OFFSET(B3,1,-1) : Returns the value in cell that is located one row down (1) and one row to the left (-1) from B3. Displays '''Apple''' as the output. | + | =OFFSET(B3,1,-1) : Returns the value in cell that is located one row down(1) and <br />one row to the left(-1) from B3. Displays '''Apple''' as the output. |
| + | |||
| + | ==Related Videos== | ||
| + | |||
| + | {{#ev:youtube|tqJWdGUjUpI|280|center|OFFSET}} | ||
== See Also == | == See Also == | ||
| Line 65: | Line 68: | ||
*[http://en.wikipedia.org/wiki/Offset_(computer_science) Offset] | *[http://en.wikipedia.org/wiki/Offset_(computer_science) Offset] | ||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 13:03, 23 August 2018
OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width)
where,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ReferenceRange} is a reference cell or base cell of the offset,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle RowsOffset} represents the number of cells up or down the reference cell,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ColumnsOffset} represents the number of cells left or right to the reference cell,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Height} is an optional value that represents the number of rows to be displayed as the output, and
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Width}
is an optional value that represents the number of columns to be displayed as the output.
- OFFSET(), returns a reference offset from a given reference.
Description
OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width)
- OFFSET function is used to display the value of cell that is specified number of rows or columns away from the reference.
- Offset reference should be within the spreadsheet, else Calci displays #NULL error message.
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle RowsOffset} can be positive or negative. If is positive, it means move down from the reference. If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle RowsOffset} is negative, it means move up from the reference.
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ColumnsOffset} can be positive or negative. If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Columns} is positive, it means move right from the reference. If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ColumnsOffset} is negative, it means move left from the reference.
- If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Height} or Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Width} is omitted, Calci assumes it to be the same Height or Width as the reference.
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Height} and Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Width} should be > 1, else Calci displays #NULL error message.
Examples
Consider the following examples that demonstrate the use of OFFSET function:
| Fruit | Color | Quantity |
| Orange | Orange | 20 |
| Banana | Yellow | 30 |
| Apple | Red | 18 |
| Strawberry | Red | 25 |
=OFFSET(A2,2,2,1,1) : Returns the value in cell that is located two rows down(2) and
one row to the right(2) from A2. Displays 18 as the output. =OFFSET(B3,1,-1) : Returns the value in cell that is located one row down(1) and
one row to the left(-1) from B3. Displays Apple as the output.
Related Videos
See Also
References