When you copy a formula from cell A10 to cell B10 What happens to the cell references in the formula quizlet?

4.6.9 Using a Formula to Set Cell Values

Beginning with version 2018, Origin supports cell-level calculations.

• Cell formulas are supported in a worksheet data cell or UserDefined Parameter Row cell.
• Cell formulas are entered directly into the cell and begin with "=" (equals sign).
• When extending cell formulas to other cells by dragging, the syntax supports both absolute, relative and mixed references.
• Cell formula notation is adapted from the spreadsheet cell notation used in Set Values.
• Cell formulas return a numeric value, a string value or a date-time value (as long as the date-time value has been properly formatted and is recognized as date-time and not text).
• The containing worksheet column Format must be set to Text & Numeric.
• To define a cell formula, Spreadsheet Cell Notation (SCN) must be enabled in the workbook. If you see this icon
  
  in the upper-left corner of the worksheet, it indicates that SCN is OFF. To enable SCN, right-click on the workbook title bar and choose Properties. On the Properties tab, check the Spreadsheet Cell Notation box.

The cell formula mechanism discussed here is not the only way to do math operations on cell values. Origin has a Set Values feature for doing row-wise math operations on partial or entire columns of data, including multiple columns. Set Values is not only faster -- it has such things as syntax coloring and assistance with functions -- things that are quite helpful when building complex expressions. Plus, you can define scripts to do such things as conditioning of data before your calculations are performed. For more information, see Set Column Values - Quick Start.

Contents

• 1 Cell Formula Notation
• 2 Edit Mode and Display of Cell Formulas
  • 2.1 Edit Cell Formulas in Formula Bar
• 3 What Can I Enter in Worksheet Cell Formulas?
  • 3.1 Cell Formula Examples
  • 3.2 Autocomplete
• 4 User Parameter Row Formula
  • 4.1 Referring to Worksheet Columns using "This"
• 5 Auto Adjustment of Cell Formulas When Inserting or Deleting Rows or Columns
• 6 Extending Formulas Across Rows or Columns
  • 6.1 Extending Formulas that Contain Sheet References
• 7 Older Methods of Referencing Worksheet Cell Values
• 8 Limitations to Use of Cell Formulas

Cell Formula Notation

All cell formulas begin with an equals sign "=":

=A1 // returns value in column A, row 1
=A1+B1 // returns total of column A, row 1 and column A, row 2
=mean(A) // returns mean of column A
=total(A[1:10]) // returns total of cells A1 through A10
=mean(A1:J10) // returns the mean of cells in the range A1 through J10.
=date(A1, "dd.MM.yyyy HH:mm:ss.##") // takes date-time string of specified format in column A row 1, returns Julian day value

Edit Mode and Display of Cell Formulas

The worksheet has an Edit Mode (from the menu, Edit: Edit Mode):

• Edit Mode allows for display and easy editing of cell formulas.
• When Edit Mode is toggled off, cell formulas are hidden and instead, the resulting cell value is displayed (note that in this mode, you can also double-click on a cell to edit the formula).

You don't have to toggle Edit Mode on to see the formula behind a particular cell value. When the cell is selected, the formula will display in the lower-left corner of the Status Bar.

Edit Cell Formulas in Formula Bar

The Formula Bar introduced in Origin 2021 is an Excel-like bar allowing you to enter cell or column formula/expressions. While it is not necessary to use the Formula Bar to enter cell expressions, it does offer the advantages of function search, interactive range selection and improved visibility of expressions, particularly of long expressions that exceed cell width. You can also define a cell or cells as range variable.

To turn on Formula Bar, with a workbook active,

• select menu View: Formula Bar.

Or

• press Ctrl+Alt+F

To enter an expression into a cell (data cell or F(x)=), click on the cell, then:

1. Enter an "=" and type your expression directly;

Or,

1. click the
   
   button to open the Search and Insert Functions dialog.
2. Search for the desired function then double-click on it to insert it into Formula Bar.

When your expression is complete, click the check button

To insert a data range in the cell expression,

1. Enter an "=" and enter your expression. Place the cursor at where you want to insert the data range and select the range by
   • clicking on a column heading, or
   • dragging to select a range of cells.
2. When your expression is complete, click the check button
   
   or press Enter.

Note: inserting data range of a different book is not supported.

To view and edit the whole cell expression when the expression is especially long,

• drag the edge of the Formula Bar to resize it.

Or,

• change the font size by changing the value of system variable @FBFS (default is "130").

To define the selected cell(s) a range variable,

• enter the range name in the left-most edit box. Refer to Named Range document for more information.

What Can I Enter in Worksheet Cell Formulas?

In addition to data references, your cell formula can include variables, operators, functions and constants.

Cell Formula Examples

Cell references in Origin, as in MS Excel, can be relative, absolute or mixed. Prior to Origin 2020, square brackets (e.g. A[1]) were used to make absolute cell references. Now, you can use the MS Excel "$" notation in absolute and mixed cell references. Brackets are still supported but most users will probably prefer the $ notation, especially if they are experienced Excel users.

Use "end" or "0" to define the last cell in the column. So, for instance, to enter a cell formula that sums all values of column C, rows 5 to the last row of the column, you could say "=total(C[5:end])" or "=total(C[5:0])".

The following are examples of expressions that can be entered into the cell.

† Excel does not use Julian dates but instead uses a "serial number" system in which "time zero" is either January 1, 1900 (Windows default) or January 1, 1904 (Mac default). For information on converting Excel dates to Origin's Julian-based system, see FAQ-283: How do I convert date data from Excel to Origin?
‡ Origin's today() function returns today's date as a Julian-date value while Excel's TODAY() function returns today's date as an Excel serial number.

Fore more information, see:

• LT Supported String Functions
• Documentation for individual string and character functions
• For side-by-side comparison of Origin and Excel functions, by category, see this page.

Note: Users should note that in Origin's built-in scripting language LabTalk, the "$" is also used to create or express a string variable.

string greeting$ = "Hello"; // creates a string variable named "greeting" and assigns to it, the value "Hello"
greeting$ =; // returns "Hello"

In the example in row 2 of the table above, two string variables are concatenated using the "+" operator. In this context, the "$" has nothing to do with absolute cell references. If the "$" is at the end of the string, it will be associated with string conversion and not cell references.

Another example in which the user might be confused by the "$" string notation: Suppose you want to return a string value "boston" from cell A1:

=A1 // returns missing value (--)
=A1$ // returns "boston"
=upper(A1$) // returns "BOSTON"

Another use for string notation: Suppose you want to return a cell value from another book. There are a couple of ways to do this (including the more straightforward [BookName]SheetName!ColNameRowIndex) but you could also use, say, [A1$]SheetName!ColNameRowIndex, where cell A1 contains the name of the source book. If cell A1 contained "Book1" then the string is converted as [Book1]. See, for instance, Extending Formulas that Contain Sheet References.

Autocomplete

For Origin 2022, cell formulas, the F(x)= label row and column formulas ( the Set Values dialog) get autocompletion support for LabTalk-supported functions and named ranges.

If you do not wish to use the feature, you can disable it for cell/F(x)= and/or column formulas:

1. Click Preferences: System Variables.
2. In an empty Variable cell, type FAC.
3. Enter one of the following in the Value cell (values are additive): 0 = turn off autocomplete, 1 = enable for cell formula/F(x)=, 2 = enable for column formula, 3 (default) = enable for cell/F(x)= and column formula.
4. Click OK to close the dialog.

User Parameter Row Formula

Beginning with Origin 2019, you can right-click on the column label row headings, Add User Parameters and name and define a formula for the entire row. This is handy for calculating key statistics (e.g. mean, std. deviation) for each column of numbers in a worksheet.

The column formula uses the placeholder "This" as a wildcard reference to each column in the worksheet (see next section).

Once a row formula has been created, you can edit the Name and Formula by right-clicking on your User Parameter row heading and choosing Edit from the shortcut menu. You can edit cell formulas individually by double-clicking directly into a User Parameter row cell.

Referring to Worksheet Columns using "This"

As mentioned in the previous section, "This" is a placeholder that is used to refer to the current dataset, i.e. the values in the associated worksheet column. We saw that "This" is used when creating a user parameter row formula and you can use "This" in your own cell and column formulas.

If you want to define a user parameter row formula and extend it across the entire row, define a Formula using the procedure outlined above. If you want to add a formula only to some user parameter cells -- you can do the following:

1. Create a User-defined Parameter row.
2. In the User-defined column cell where you want to add a formula, enter your formula using "This" to refer to the data in the current column (e.g. =max(This)  ).
3. Click outside the cell to exit cell-edit mode. A calculated value should display.
4. With the cell selected, hover on the lower right corner of the cell then (a) grab the "+" handle and drag with your mouse to extend the calculation to other worksheet columns or (b) double-click on the "+" handle to extend the formula to every row cell to the right of the original formula cell.

Note that "This" can also be used in worksheet data cells, as for instance, at the end of a column of data (e.g. "=total(This)"). It cannot be used to represent data in row-wise calculations.

Auto Adjustment of Cell Formulas When Inserting or Deleting Rows or Columns

Inserting or deleting of columns or rows triggers automatic adjustment of column formulas.

Extending Formulas Across Rows or Columns

Extending a cell formula across many rows and columns can strain system resources and may cause Origin to freeze. In many cases, you can accomplish the same task using more easily using the F(x)= cell or the (equivalent but more powerful) Set Values dialog box.

Once you have entered a cell formula into a cell, it is possible to extend the formula to cells in other rows and columns, (1) hovering on the lower-right corner of the formula cell and (2) when the cursor becomes a "+", drag with your mouse to extend down, across or both.

• When you drag a cell formula to autofill other cells, column and row references are adjusted in relative fashion. That is, if you drag "=A1 + B1" from cell C1 to cell D1, the formula in D1 is adjusted and becomes "=B1 + C1". In such instances, cell references are said to be relative.
• To make absolute references to a cell value, place a "$" character before both the column reference and the row reference (e.g. =$A$1). Dragging "=$A$1" to other cells does not adjust the cell reference. It will equal the value in column A, row 1 in every cell to which it is copied (dragged).
• To make mixed references to a cell value, place the "$" character in front of that element -- column or row -- that you do not want adjusted. For instance, when dragging a formula containing "$A1" to cells in other rows and columns, the column reference "A" will be locked while the row reference "1" will be incremented by one for each successive row.

You can also use "[i]" to create relative row references: "[i]" is a variable that is recognized as a reference to the current row (i.e. a cell formula of "=B[i]" will return the value in column B, in the same row in which the cell formula appears). The following figure illustrates equivalent forms that will return the same values.

Rather than dragging to extend a formula down a long column of values, hover over the lower right corner of the first formula-containing cell and double-click on the crosshairs.

Extending Formulas that Contain Sheet References

Note that when dragging cell formulas that contain sheet references, row and column references auto-adjust while sheet references do not (see following example):

This won't be an issue for most users but occasionally, a user wants to reference a particular cell on multiple sheets and when trying to fill a column with values by dragging with the mouse, the sheet reference fails to auto-adjust. While there is no direct method of auto-adjusting sheet references, there is a workaround. This involves filling a worksheet column with the needed sheet names (strings), then using a special syntax to concatenate those strings with a column and row fragment that -- when combined with the sheet string -- forms a complete cell reference.

To quickly review, Origin's dataset naming syntax (range notation) allows two ways of referring to the worksheet -- by sheet name or by sheet index. Thus, Sheet1 can be referred to by Sheet1! or simply 1!. Note the exclamation mark (!) which is used in range notation to separate sheet from column[row] (e.g. Sheet1!Col(1)[1] or Sheet1!A[1] or Sheet1!A1).

In the above example, we have auto-filled column A with row numbers and column B with sheet names. We can combine references to these cells with a "!Col[row]" fragment that resolves to "1!Col[row]" or "SheetN!Col[row]".

The general form of our concatenated string is: ==SheetOrIndex$ + "!ColumnRow" (or "!Column[Row]")

Note that your sheet strings will need to be on the same page where you build your auto-adjusting sheet references.

Older Methods of Referencing Worksheet Cell Values

There are older methods for inserting cell values, variable values and strings into worksheet cells and, in a limited way, these methods will support calculations (e.g. var://max(col(A))*2). Most users will find the newer notation to be much more useful but we point these things out so that the user is aware.

• Display the contents (including strings) of other workbook cells.
• Display variable values in workbook cells.

Limitations to Use of Cell Formulas

Beginning with Origin 2018, cell formulas give users a way to perform cell-level calculations incorporating cell values, functions, variables and constants, using a syntax that is similar (but not identical) to MS Excel.

Things that are not supported:

• Importing an MS Excel file will not bring in your Excel formulas. As of Origin 2018 SR0, only calculated values are imported. See Working with Microsoft Excel.
• Cell formulas are not supported in the Long Name, Units, Comments or system Parameters rows of the Column Label Rows portion of the worksheet. They are only supported in User-defined Parameter rows.
• In Origin, the variable i = row index and the variable j = column index (see System Variables). Formulas incorporating the row index variable i can be extended down a column by dragging (e.g. "=B[i] - C[i]") but formulas that combine multi-column range references and the variables i or j (e.g. "=total(A[i]:D[i])") are NOT supported.

There are "@" two system variables that the user should be aware of: To disable cell formulas in the current session, open the Script window (Window: Script Window) and type: @esc=0 <Enter> To disable support for Excel-style notation (cell and column references such as "A1:C10") in the current session, open the Script window (Window: Script Window) and type: @xlr=0 <Enter> To have either or both of these features disabled every time Origin starts, see FAQ-708: How do I permanently change the value of an "@" system variable?

When you copy a formula to another cell and the cells in the formula change the cells are known as relative cell references?

Which type of cell references are automatically updated when copied?

Which of the following formulas totals the cells B6 B7 B8 B9 and B10 most efficiently?

How do you edit the formula in a cell so the references to another cell will update when the formula is copied?