Definition, Uses and Examples of Functions in Excel

Nesting the ROUND and SUM Functions in Excel
Nesting the ROUND and SUM Functions in Excel. Ted French

The short answer: a function is a preset formula in Excel and Google Spreadsheets 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 Spreadsheets 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.

This is accomplished by using the SUM function as the ROUND function's Number argument.

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

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

  1. find the sum of the values in cells D1 to D6;
  1. round this result to two decimal places.

Since Excel 2007, up to 64 levels of nested functions are permitted. In versions prior to this, 7 levels of nested functions were allowed.

Worksheet vs. Custom Functions

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

  • Worksheet Functions
  • Custom or User Defined Functions

Worksheet functions are the ones native 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 located on the Developer tab of the ribbon.

Google Spreadsheets' 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 UDF is 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. In doing so, they cannot execute commands that in any way 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 Spreadsheets does not currently support them, in Excel, a macro is a series of recorded steps that automates repetitive worksheet tasks - such as formatting data or copy and paste operations  - by imitating keystrokes or mouse actions.

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

    1. UDF's perform calculations while macros carry out actions. As mentioned above, UDF's cannot perform operations that affect the program's environment while macros can.
    2. In the Visual Basic editor window, the two can be differentiated because:
      • UDF's begin with a Function statement and end with End Function;
      • Macros begin with a Sub statement and end with End Sub.
    Format
    mla apa chicago
    Your Citation
    French, Ted. "Definition, Uses and Examples of Functions in Excel." ThoughtCo, May. 14, 2017, thoughtco.com/description-of-function-3123849. French, Ted. (2017, May 14). Definition, Uses and Examples of Functions in Excel. Retrieved from https://www.thoughtco.com/description-of-function-3123849 French, Ted. "Definition, Uses and Examples of Functions in Excel." ThoughtCo. https://www.thoughtco.com/description-of-function-3123849 (accessed September 23, 2017).