Definitions, Uses, and Examples of Functions in Excel and Google Sheets

A function is a preset formula in Excel and Google Sheets that is intended to carry out specific calculations in the cell in which it is located.

Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments. Like all formulas, functions begin with the equal sign ( = ) followed by the function's name and its arguments:

• The function name tells Excel what calculations to perform.
• The arguments are contained inside parentheses or round brackets and tell the function what data to use in those calculations.

For example, one of the most used functions in Excel and Google Sheets is the SUM function:

= SUM ( D1 : D6 )

In this example:

• The name tells Excel to add together the data in selected cells.
• The argument (D1:D6) function adds the contents of cell range D1 to D6.

Nesting Functions in Formulas

The usefulness of Excel's built-in functions can be expanded by nesting one or more functions inside another function in a formula. The effect of nesting functions is to permit multiple calculations to take place in a single worksheet cell.

To do this, the nested function acts as one of the arguments for the main or outermost function. For example, in the following formula, the SUM function is nested inside the ROUND function.

= ROUND(SUM (D1 : D6), 2)

When evaluating nested functions, Excel executes the deepest or innermost function first and then works its way outward. As a result, the formula above will now:

• Find the sum of the values in cells D1 to D6.
• Round this result to two decimal places.

Worksheet vs. Custom Functions

There are two classes of functions in Excel and Google Sheets:

• Worksheet Functions
• Custom or User Defined Functions

Worksheet functions are ones built-into to the program, such as the SUM and ROUND functions discussed above. Custom functions, on the other hand, are functions written, or defined, by the user.

In Excel, custom functions are written in the built-in programming language: Visual Basic for Applications or VBA for short. The functions are created using the Visual Basic editor, which is installed with Excel.

Google Sheets' custom functions are written in Apps Script, a form of JavaScript, and are created using the script editor located under the Tools menu.

Custom functions usually, but not always, accept some form of data input and return a result in the cell where it is located.

Below is an example of a user-defined function that calculates buyer discounts written in VBA code. The original user-defined functions, or UDFs, are published on Microsoft's website:

Function Discount(quantity, price)
If quantity >=100 Then
Discount = quantity * price * 0.1
Else
Discount = 0
End If
Discount = Application.Round(Discount, 2)
End Function

Limitations

In Excel, user-defined functions can only return values to the cell(s) in which they are located. They cannot execute commands that change the operating environment of Excel, such as modifying the contents or formatting of a cell.

Microsoft's knowledge base lists the following limitations for user-defined functions:

• Inserting, deleting, or formatting cells in a worksheet.
• Changing the value of data in another cell.
• Moving, renaming, deleting, or adding sheets to a workbook.
• Changing any environment options, such as the calculation mode or screen views.
• Setting properties or executing most methods.

User Defined Functions vs. Macros in Excel

While Google Sheets does not currently support them, in Excel, macros are a series of recorded steps that automate repetitive worksheet tasks. Examples of tasks that can be automated include formatting data or copy and paste operations.

Even though both utilize Microsoft's VBA programming language, they are different in two respects:

1. UDFs perform calculations, while macros carry out actions. As mentioned above, UDFs cannot perform operations that affect the program's environment, while macros can.
   1. In the Visual Basic editor window, the two can be differentiated because:
      UDFs begin with a Function statement and end with End Function.
   2. Macros begin with a Sub statement and end with End Sub.