A function is a special kind of formula, for this reason a function"s debates must follow the rules for entering formulas. The arguments for a function can be:

number message strings Logical worths Error worths cabinet or range references selection constants

A function may need its an initial argument to be a number, the second argument a message string, and also the 3rd argument a cabinet reference. Some debates can be of much more than one type; because that example, the amount function"s argument can be a collection of numbers, variety references, or range constants. Refer to the documentation ~ above the individual function to identify the form of data required for the duty you space entering.

number in disagreements

normally you can go into a number in an argument by simply typing the number. Do not use commas to different thousands, due to the fact that the comma will be interpreted as separating 2 arguments.

You are watching: What do you use to separate arguments in a function?

Negative numbers are gone into using the negative sign (-). Perform not use parentheses: the function will translate the value as a different argument.

Fractions have the right to be entered by utilizing a cut for the portion separator (3/4 is evaluated as .75).

Percentages deserve to be entered using the percent sign (34% is evaluated together .34).

Scientific notation deserve to be offered for very huge or very little numbers (34E+09 is evaluated as 34,000,000,000).

Exponents deserve to be entered using the caret authorize (3^4 is evaluate as 3 to the 4th power, or 81).

message Strings in arguments

message strings in an discussion list should be attached in quotation marks. There is no the quotation marks, the role will return the #NAME? error. As soon as entering multiple text arguments, be certain the closing quotation mark comes before the comma that separates two arguments.

as soon as the argument is a cell recommendation pointing to a cabinet containing a message string, the message in the cell does not have to be enclosed in quotes.

If a number is encountered once text is expected, the number is convert to text. "The number is "&3 is taken as The number is 3. If message is encountered once a number is expected, the text is converted to a number (1 + "3" is construed as 4). If the text cannot be convert to a valid number (e.g., 1 + "Text"), the role will return the #VALUE! error.


You may use the ampersand (&) character to concatenate text strings once a text dispute from two various sources is required. For example, in the spreadsheet below, cell A3 uses concatenation to produce the text very first quarter.


Logical worths in arguments

The logical value TRUE converts to 1, while FALSE converts to 0.

If a number is encountered when a logical value is expected, 0 is evaluated as FALSE and also all other numbers space evaluated as TRUE. If text is encountered when a logical value is expected, "TRUE" is evaluated as TRUE, "FALSE" is evaluated together FALSE, and also all various other text return the #VALUE! error.

Cell references in disagreements

For most arguments, you have the right to substitute a cell or range reference for the data compelled by an argument. A referral is a cell"s address. It identifies a cabinet or selection of cell by introduce to the shaft letter and also row variety of the cell(s). For example, A1 refers to the cabinet at the intersection of pillar A and also row 1.

The reference tells Formula One for Java to usage the components of the referenced cell(s) as the function"s argument. For example, if an debate requires a number, you can substitute a referral to a cabinet that includes a number. The number in the referenced cabinet is offered in the calculation of the function. The referenced cell should contain the ideal data for the discussion that offers it.

friend specify a selection of cells by put a colon (:) between two cell references. For example, A1:C3 refers to the selection anchored by cells A1 and C3. The selection includes all cells in columns A, B, and C that rows 1, 2, and also 3.


Some functions may take much more than one cell together an argument. Because that example, once the AVERAGE function has a selection reference together its argument, the role averages the data in every the cells in the range.

empty Cells

Most features ignore any kind of empty cells uncovered in a range referenced in one argument. However, cell that appear empty and also are not may impact the results of the function. Because that example, cell containing empty message or message consisting only of spaces may be treated as text. Cells that room formatted not to display zero values might contain concealed zeroes that will certainly be treated as numeric values. If your function displays unexpected results, examine for empty cells and also cells that appear empty.

Entering cell References

girlfriend can go into cell referrals in debates in three ways:

type in the cabinet or range address. form in the name of a called cell or range. For info on defining names, watch utilizing Names. use the computer mouse to click and also drag ~ above cells and ranges. Formula One because that Java instantly enters a relative recommendation identifying the cell(s) you select.

Absolute and Relative References

There are two species of cabinet references: relative and also absolute.

Relative references point to a cell based upon its position relative to the current cell. Once the cell containing the referral is relocated or copied, the recommendation is changed to point to a brand-new cell with the same relative balance out as the initially referenced cell. for example, suppose the duty SUM(B1:B3) is located in cabinet A1. Once you copy the duty and dough it down two rows into cell A3, the duty will be adjusted down two rows, to SUM(B3:B5). Relative recommendations will be changed whenever you cut or copy and also paste a function or when you usage the edit > Fill regulates to to fill a selection with a copy of a function. Absolute references suggest to a cabinet at an accurate location. As soon as a cell containing a formula through absolute references is relocated or copied, the reference does not change. Absolute references have actually a dollar authorize ($) in former of the heat number and/or obelisk letter.

referrals that are part absolute and component relative are referred to as mixed references. The following table list the recommendation types. Reference kind
A1 Relative recommendation pointing to cell A1.
$A$1 Absolute recommendation pointing to cabinet A1.
$A1 Absolute pillar reference, relative row referral pointing to cell A1.
A$1 Relative column reference, pure row recommendation pointing to cabinet A1.

using Relative and also Absolute References

You deserve to copy and paste absolute, relative, and also mixed referrals to produce worksheets that are straightforward to update and that are smaller sized than worksheets wherein each formula is developed separately.

because that example, in the following worksheet, the values in tower A need to be multiply by the percentages in row 1.


To do these calculations, you could enter the function PRODUCT(A2,B1) in cabinet B2, PRODUCT(A3,B1) in cell B3, PRODUCT(A4,B1) in cell B4, etc. Besides the truth that this would be a many typing, this systems would require Formula One for Java to store nine different formulas in memory.

A far better way to execute it would be to get in the role PRODUCT($A2,B$1) in cabinet B2, and use the worksheet"s edit > fill command to fill cells B2 through D4 with copies of that function. As soon as the function is replicated in this manner, that relative references change, however the absolute recommendations stay the same.


The resulting worksheet calculates every the figures using multiple duplicates of that one function. Only one role must be preserved in memory.


If you change the percentages in heat 1 or the figures in pillar A, the calculations in the worksheet will automatically adjust because of the absolute referrals to those cells. This makes the worksheet easy to update.

references to various other Worksheets

You deserve to reference cell in various other worksheets in the very same workbook by put an exclamation mark between the paper name and also the reference. The sheet surname is the name uncovered on the worksheet tab. For example, Data!A1 refers to the height left cell in a sheet referred to as Data.

Sheet names v spaces. If the sheet name includes spaces, you have to enclose the surname in single quotes: `1994 sales"!B17.

Cells on 2 worksheets. You deserve to make a recommendation to cell on two or an ext different worksheets by place a colon between the two sheet names. Because that example, Sheet1:Sheet2!A1 describes two cells: cell A1 in Sheet1 and cell A1 in Sheet2.

Order of paper names. References to much more than one worksheet have to list the worksheets in the stimulate in i m sorry they show up in the workbook.


references to other Workbooks

recommendations that allude to cells on worksheets in other workbooks are referred to as external references.

An exterior reference is produced by put the workbook name in brackets, followed by the worksheet name, one exclamation point, and finally a cell or range reference.


external references will work-related only if both workbooks are open up in the workbook designer. If the referenced workbook is not open once you create the external reference, one Invalid Formula Syntax error article will appear.

developers who desire to use outside references when they are not making use of the Workbook Designer control must produce a group of workbooks using the setGroup method in the view class. Every workbooks that describe each other have to be included to the same team in order for the exterior references come work.

The complying with are examples of external references utilizing absolute, relative, and also mixed references. Reference form
1987!A1 Relative reference pointing to cabinet A1 in a worksheet title 1987 that a workbook title Sales.
January!$A$1 Absolute reference pointing to cabinet A1 in a worksheet titled January that a workbook title FY91.
Sheet1:Sheet2!$A1 Absolute obelisk reference, loved one row recommendation pointing to cell A1 in the an initial and 2nd worksheets the a workbook titled Q1.
Sheet1:Sheet4!A1:F1 Relative row and also column referral pointing to the selection A1 come F1 in the an initial four worksheets of a workbook title Store1.

courses in external References

~ you go into an exterior reference, Formula One for Java will readjust the layout of the referral to present the absolute course to the workbook girlfriend referenced. For example, speak you gone into this recommendation to a workbook named September in the Payroll magazine on her C drive:


after ~ you go into that reference, if you return to the cell where the referral was entered, you will note that Formula One for Java has readjusted it to:


This absolute path is taped in the worksheet. If you later on move the September workbook, the external reference have to still work, as lengthy as you open up September.vts in the Workbook Designer at the same time together the workbook that referrals it.

See more: What Is The Most Basic Unit Of Life, Is The Basic Unit Of Life

selection Constants in disagreements

For many arguments, you can substitute an array consistent for the data required. An array continuous is a perform of numbers enclosed in curly base . The function treats each item in an array constant as an individual bit of data, simply as it treats the data in each cell in a selection reference debate individually.

friend can go into an array constant that functions like a variety reference, v cells and also columns. Usage commas to separate individual cells on the same row; usage semicolons to different rows.

because that example, the array consistent 2,4,6,8;10,12,14,16;18,20,22,24 is indistinguishable to this variety reference: