What Feature Of Excel Allows You To Automatically Calculate Common Formulas With Selected Data

Lesson 10: Working with Basic Functions

/en/excel2010/creating-complex-formulas/content/

Working with bones functions

Figuring out formulas for calculations you want to make in Excel can be tedious and complicated. Fortunately, Excel has an entire library of functions—or predefined formulas—you tin can take advantage of. You may exist familiar with common functions like sum, boilerplate, product, and count, simply there are hundreds of functions in Excel, even for things like formatting text, referencing cells, computing financial rates, and analyzing statistics.

In this lesson, y'all'll larn the basics of inserting mutual functions into your worksheet past utilizing the AutoSum and Insert Functions commands. You will also go familiar with how to search and find various functions, including exploring Excel'south Functions Library.

Bones functions

A function is a predefined formula that performs calculations using specific values in a particular order. 1 of the key benefits of functions is that they can save you fourth dimension because you exercise non accept to write the formula yourself. Excel has hundreds of functions to help with your calculations.

To use these functions correctly, you lot demand to understand the different parts of a function and how to create arguments in functions to calculate values and cell references.

You lot can download this example for extra do.

The parts of a function

The society in which y'all insert a function is important. Each function has a specific order—called syntax—which must exist followed in order for the office to work correctly. The bones syntax to create a formula with a office is to insert an equals sign (=), part name (SUM, for example, is the function name for improver), and argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.

Syntax of a basic role

Working with arguments

Arguments must be enclosed in parentheses. Private values or cell references inside the parentheses are separated by either colons or commas.

• Colons create a reference to a range of cells.

  For example, =AVERAGE(E19:E23) would calculate the average of the cell range E19 through E23.

• Commas separate individual values, jail cell references, and jail cell ranges in parentheses. If at that place is more than 1 argument, you lot must separate each statement past a comma.

  For example, =COUNT(C6:C14,C19:C23,C28) volition count all the cells in the three arguments that are included in parentheses.

To create a basic function in Excel:

1. Select the jail cell where the reply volition appear (F15, for example).
2. Type the equals sign (=), and so enter the function proper name (SUM, for instance).

   Creating a SUM function

3. Enter the cells for the argument inside the parentheses.

   Adding cells to the office argument

4. Press Enter, and the upshot will announced.

   Outcome

Excel will not always tell you if your function contains an mistake, so it's upward to yous to cheque all of your functions. To acquire how to do this, read the Double-Cheque Your Formulas lesson from our Excel Formulas tutorial.

Using AutoSum to select common functions

The AutoSum command allows you to automatically return the results for a range of cells for common functions like SUM and Average.

1. Select the cell where the answer will appear (E24, for case).
2. Click the Home tab.
3. In the Editing group, click the AutoSum drop-down arrow and select the function you want (Average, for example).

   AutoSum command

4. A formula volition appear in E24, the selected cell. If logically placed, AutoSum will select your cells for you. Otherwise, you will need to click the cells to choose the statement yous want.

   AutoSum selects and displays prison cell range

5. Press Enter, and the result will appear.

   Result

The AutoSum command can likewise be accessed from the Formulas tab.

You tin can as well apply the Alt+= keyboard shortcut instead of the AutoSum command. To use this shortcut, hold downwards the Alt key so press the equals sign.

Sentinel the video beneath to see this shortcut in action.

The Office Library

There are hundreds of functions in Excel, merely only some will exist useful for the type of data you lot're working with. In that location is no need to learn every single function, but yous may want to explore some of the different types to get ideas about which ones might be helpful to you as you create new spreadsheets.

A great identify to explore functions is in the Function Library on the Formulas tab. Here, you can search and select Excel functions based on categories such as Financial, Logical, Text, and Date & Fourth dimension. Click the buttons in the interactive below to acquire more.

Appointment & Time

The Date & Fourth dimension category contains functions for working with dates and fourth dimension and will return results like the current date and time (NOW) or the seconds (2nd).

AutoSum

The AutoSum command allows you to automatically render results for common functions.

Recently Used

Use the Recently Used command to access functions you accept recently worked with.

Financial

The Fiscal category contains functions for financial calculations like determining a payment (PMT) or interest rate for a loan (Rate).

More Functions

More Functions contains additional functions nether categories for Statistical, Engineering science, Cube, Information, and Compatibility.

Math & Trig

The Math & Trig category includes functions for numerical arguments. For instance, you can round values (ROUND), find the value of Pi (PI), multiply (Production), and subtotal (SUBTOTAL).

Lookup & Reference

The Lookup & Reference category contains functions that will return results for finding and referencing. For example, you tin add a hyperlink (HYPERLINK) to a cell or return the value of a particular row and column intersection (INDEX).

Text

The Text category contains functions that work with the text in arguments using tasks similar converting text to lowercase (LOWER) or replacing text (REPLACE).

Logical

Functions in the Logical category bank check arguments for a value or status. For instance, if an order is more than than $50 add together $4.99 for aircraft, but if it is more $100 exercise not charge for shipping (IF).

Insert Office

The Insert Function control allows y'all to hands search for a command by entering a description of what you are looking for.

To insert a function from the Part Library:

1. Select the jail cell where the answer will appear (I6, for instance).
2. Click the Formulas tab.
3. From the Role Library grouping, select the function category you want. In this example, we'll choose Engagement & Fourth dimension.
4. Select the desired function from the Engagement & Time drib-downwards menu. We'll choose the NETWORKDAYS function to count the days between the society appointment and receive appointment in our worksheet.

   Part Library Date & Time category

5. The Function Arguments dialog box will announced. Insert the cursor in the offset field, then enter or select the jail cell(s) you want (G6, for instance).

   Selecting cell for the Start_date field

6. Insert the cursor in the adjacent field, then enter or select the cell(s) you want (H6, for example).

   Selecting jail cell for the End_date field

7. Click OK, and the consequence will appear. Our results show that information technology took five days to receive the social club.

   Upshot

The Insert Function command

The Insert Function control is convenient because it allows you to search for a role by typing a clarification of what you're looking for or past selecting a category to peruse. The Insert Function command can as well be used to easily enter or select more than one statement for a function.

Using the Insert Office command

In this example, we desire to find a function that volition count the total number of supplies listed in the Office Supply Order Log. The basic COUNT role just counts cells with numbers; we want to count the cells in the Office Supply column, which uses text. Therefore, we'll need to detect a formula that counts cells with text.

1. Select the jail cell where the answer volition appear (A27, for instance).
2. Click the Formulas tab, then select the Insert Office command.

   Insert Function command

3. The Insert Function dialog box will appear.
4. Type a description of the role you are searching for, then click Go (Count cells with text, for example). You can too search by selecting a category.

   Searching for a office

5. Review the results to find the function you want (COUNTA, for example). Click OK.

   Reviewing part search results

6. The Role Arguments dialog box will appear. Insert the cursor in the beginning field, then enter or select the prison cell(s) you want (A6:A14, for example).

   Selecting cell range for Value1 field

7. Insert the cursor in the side by side field, so enter or select the cell(south) yous want (A19:A23, for example). You can continue to add additional arguments if needed.

   Selecting jail cell range for Value2 field

8. Click OK, and the result will appear. Our results show that xiv Full Supplies were ordered from our log.

   Upshot

If you're comfortable with basic functions, you may want to try a more avant-garde ane similar VLOOKUP. You can check out our article on How to Use Excel's VLOOKUP Function for more information. If y'all want to learn fifty-fifty more about functions, cheque out our Excel Formulas tutorial.

Challenge!

1. Open an existing Excel 2010 workbook. If you lot want, you tin can employ this example.
2. Create a role that contains more than ane argument.
3. Use AutoSum to insert a function. If yous are using the example, insert the MAX role in jail cell E15 to discover the highest-priced supply.
4. Insert a function from the Functions Library. If you are using the instance, find the Production function (multiply) to summate the Unit Quantity times the Unit Price in cells F19 through F23.
5. Apply the Insert Role control to search and explore functions.

/en/excel2010/sorting-data/content/

Source: https://edu.gcfglobal.org/en/excel2010/working-with-basic-functions/1/

Posted by: danielsgaince.blogspot.com

Share this post