Function definitions, usage and examples in Excel and Google Docs

A function is a predefined formula in Excel and Google Sheets that is designed to perform specific calculations in the cell it resides in.

Remark : . The information in this article applies to Excel 2019, Excel 2016, Excel 2013, and Google Sheets.

Content
  1. Function Syntax and Arguments
  2. Nested functions in formulas
  3. Worksheet vs User Defined Functions
  4. Limits
  5. Custom Functions and Macros in Excel

Function Syntax and Arguments

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments. Like all formulas, functions start with an equal sign ( = ) followed by the function name and its arguments:

  • The name of the function tells Excel which calculations to perform.

  • The arguments are enclosed in parentheses or parentheses and tell the function what data to use in these calculations.

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

 = СУММА (D1: D6) 

In this example:

  • The name tells Excel to sum the data in the selected cells.

  • argument function ( D1: D6 ) adds the contents of a range of cells D1 in D6 .

Nested functions in formulas

You can extend the usefulness of Excel’s built-in functions by nesting one or more functions within another function in a formula. The effect of nested functions is that multiple calculations can be performed in the same worksheet cell.

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

 = КРУГЛЫЙ (СУММА (D1: D6), 2) 

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

  • Find the sum of values ​​in cells of D1 before D6 .

  • Round this result to two decimal places.

As of Excel 2007, a maximum of 64 levels of nested functions were allowed. In previous versions, seven levels of nested functions were allowed.

Worksheet vs User Defined Functions

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

  • Worksheet Features

  • Custom or custom features

Worksheet functions are native to the program, such as the functions SUM and ROUND described above. On the other hand, user-defined functions are functions written or defined by the user.

In Excel, user-defined functions are written in the built-in programming language: Visual Basic for Applications or VBA for short. Functions are created using the Visual Basic Editor, which is installed with Excel.

Google Sheets custom features are written in application script a JavaScript form, and are created using the script editor in the menu Tools .

Custom functions usually, but not always, take some form of input and return the result to the cell where they are located.

The following is an example of a custom function that calculates customer discounts written in VBA code. Original user-defined functions or custom features , published on the Microsoft website:

 Функция Discount (количество, цена) 
Если количество> = 100, то
Скидка = количество * цена * 0,1
Остальное
Скидка = 0
End If Discount = Application.Round (Discount, 2)
Функция завершения

Limits

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

The Microsoft Knowledge Base lists the following limits for custom functions:

  • Insert, delete, or format cells in a worksheet.

  • Change the data value in another cell.

  • Move, rename, delete or add sheets to a workbook.

  • Change any environment settings, such as calculation mode or screen displays.

  • Set properties or run most methods.

Custom Functions and Macros in Excel

While Google Sheets doesn’t currently support them, macros in Excel are a series of included steps that automate repetitive tasks on a sheet. Examples of tasks that can be automated include data formatting or copy and paste operations.

While both use Microsoft’s VBA programming language, they differ in two respects:

  1. Custom functions perform calculations, while macros perform actions. As mentioned above, user-defined functions cannot perform operations that affect the environment of the program, while macros can.

  2. You can tell them apart in the Visual Basic Editor window because:

    1. Custom functions start with function and end end of function .

    2. Macros start with Sub and end end sub .

Leave a Reply

Your email address will not be published.