Difference between revisions of "Manuals/calci/OFFSET"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''OFFSET'''(Reference, Rows, Columns, Height, Width) where, '''Reference''' - represents the referencefrom b...")
 
 
(8 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
<div style="font-size:30px">'''OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width)'''</div><br/>
  
−
'''OFFSET'''(Reference, Rows, Columns, Height, Width)
+
where,
 +
*<math>ReferenceRange</math> is a reference cell or base cell of the offset,
 +
*<math>RowsOffset</math> represents the number of cells up or down the reference cell,
 +
*<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>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.
 +
 
 +
== Description ==
 +
 
 +
OFFSET (ReferenceRange,RowsOffset,ColumnsOffset,Height,Width)
  
−
where,
+
*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.
 +
*<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>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 or Width as the reference.
 +
*<math>Height</math> and <math>Width</math> should be &gt; 1, else Calci displays #NULL error message.
  
−
'''Reference''' - represents the referencefrom base of the offset.
+
== Examples ==
  
−
'''Rows''' - the number of rows, up or down.
+
Consider the following examples that demonstrate the use of OFFSET function:
  
−
'''Columns''' - the number of columns, to the left or right.
+
<div id="2SpaceContent" class="zcontent" align="left">
  
−
'''Height''' - the height, in number or rows.
+
{| id="TABLE3" class="SpreadSheet blue"
 +
|- class="even"
 +
| class="sshl_f" |'''Fruit'''
 +
| class="sshl_f" |'''Color'''
 +
| class="sshl_f" |'''Quantity'''
  
−
'''Width''' - the width, in number of columns.
+
|- class="odd"
 +
| class="sshl_f" |Orange
 +
| class="sshl_f" |Orange
 +
| class="sshl_f" |20
  
−
</div>
+
|- class="even"
−
----
+
| class="sshl_f" |Banana
−
<div id="1SpaceContent" class="zcontent" align="left">
+
| class="sshl_f" |Yellow
 +
| class="sshl_f" |30
  
−
It returns a refrence to a range i.e a specified number of rows and column from a cell.
+
|- class="odd"
 +
| class="sshl_f" |Apple
 +
| class="sshl_f" |Red
 +
| class="sshl_f" |18
  
−
</div>
+
|- class="even"
−
----
+
| class="sshl_f" |Strawberry
−
<div id="7SpaceContent" class="zcontent" align="left">
+
| class="sshl_f" |Red
 +
| class="sshl_f" |25
 +
|}
  
−
If height or width is omitted, it cosider as a Reference.
+
=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 <br />one row to the left(-1) from B3. Displays '''Apple''' as the output.
  
−
</div>
+
==Related Videos==
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
  
−
OFFSET
+
{{#ev:youtube|tqJWdGUjUpI|280|center|OFFSET}}
  
−
</div></div>
+
== See Also ==
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left">
 
  
−
Lets see an example in (Column3, Row3)
+
*[[Manuals/calci/ADDRESS | ADDRESS]]
 +
*[[Manuals/calci/INDIRECT | INDIRECT]]
  
−
<nowiki>=OFFSET(B2,2,1,1,1)</nowiki>
+
== References ==
 +
 
 +
*[http://en.wikipedia.org/wiki/Offset_(computer_science) Offset]
  
−
OFFSET returns 9.
 
  
−
</div>
 
−
----
 
−
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
 
−
----
 
−
<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>
 
−
----
 
−
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
 
−
----
 
−
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
 
−
----
 
−
<div id="2SpaceContent" class="zcontent" align="left">
 
  
−
{| id="TABLE3" class="SpreadSheet blue"
+
*[[Z_API_Functions | List of Main Z Functions]]
−
|- class="even"
 
−
| class=" " |
 
−
| Column1
 
−
| class="    " | Column2
 
−
| class="    " | Column3
 
−
| Column4
 
−
|- class="odd"
 
−
| class=" " | Row1
 
−
| class=" " | 1
 
−
| class=" " | 7
 
−
| class="sshl_f" |
 
−
|
 
−
|- class="even"
 
−
| class="  " | Row2
 
−
| class=" " | 2
 
−
| class=" " | 8
 
−
| class="  " |
 
−
|
 
−
|- class="odd"
 
−
| Row3
 
−
| class=" " | 3
 
−
| class=" " | 9
 
−
| class="sshl_f" | 9
 
−
|
 
−
|- class="even"
 
−
| Row4
 
−
| class="                                                sshl_f " | 4
 
−
| class=" " | 10
 
−
| class="                                          SelectTD ChangeBGColor SelectTD" |
 
−
<div id="2Space_Handle" class="zhandles" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" class="zhandles" title="Click and Drag over to AutoFill other cells."></div><div id="2Space_Drag" class="zhandles" title="Click and Drag to Move/Copy Area.">[[Image:copy-cube.gif]]  </div>
 
−
|
 
−
|- class="odd"
 
−
| class=" " | Row5
 
−
| class=" " | 5
 
−
| class=" " | 11
 
−
|
 
−
| class=" " |
 
−
|- class="even"
 
−
| Row6
 
−
| class=" " | 6
 
−
| class=" " | 12
 
−
|
 
−
|
 
−
|}
 
  
−
<div align="left">[[Image:calci1.gif]]</div></div>
+
*[[ 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

OFFSET

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/OFFSET&oldid=211214"