Excel 2013 Level 1 Unit 1 Preparing and Formatting a Worksheet

2. Excel 2013Level 1 Unit 1 Preparing and Formatting a Worksheet Chapter 2 Inserting Formulas in a Worksheet

3. Inserting Formulas in a Worksheet Quick Links to Presentation Contents • Write Formulas • Insert Formulas with Functions • Write Formulas with Statistical Functions • CHECKPOINT 1 • Write Formulas with NOW and TODAY Functions • Display Formulas • Use Absolute and Mixed Cell References • CHECKPOINT 2

4. Write Formulas

5. Write Formulas - continued • If a formula contains two or more operators, Excel uses the same order of operations used in algebra. • From left to right in a formula, this order, called the order of operations, is: negations (negative number—a number preceded by a minus sign) first, then percents, then exponentiations, followed by multiplications, divisions, additions, and finally subtractions.

6. Write Formulas - continued Copy a Formula Relatively: • Insert formula in cell. • Select cell containing formula and all cells you want to contain formula. • Click Fill button. • Click desired direction. Fill button

7. Write Formulas - continued To copy a relative formula using the fill handle: • Insert formula in cell. • With cell active, position mouse pointer on fill handle. • Hold down left mouse button, drag and select desired cells, and then release mouse button. fill handle

8. Write Formulas - continued Write a formula by pointing: • Click cell that will contain the formula. • Type equals sign. • Click cell you want to reference in formula. • Type desired mathematical operator. • Click next cell reference. • Press Enter. formula

9. Write Formulas - continued To use the Trace Error button: • Click Trace Error button when it appears. • Select an option from drop-down list. Trace Error button

10. Write Formulas - continued • Excel is a sophisticated program that requires data input and formula creation to follow strict guidelines in order to function properly. • When guidelines that specify how data or formulas are entered are not followed, Excel will display one of many error codes. • When an error is identified with a code, determining and then fixing the problem is easier than if no information is provided.

11. Write Formulas - continued

12. Insert Formulas with Functions • =SUM(B2:B5) is an example of a formula. The beginning section of the formula, =SUM, is called a function, which is a built-in formula. • A function operates on what is referred to as an argument. • An argument may also contain a constant. • When a value calculated by the formula is inserted in a cell, this process is referred to as returning the result.

13. Insert Formulas with Functions - continued FORMULAS tab

14. Insert Formulas with Functions - continued To insert a function: • Position insertion in desired cell. • Click Insert Function button. • At Insert Function dialog box, choose function category. • Choose function. • Click OK. continues on next slide… Insert Function dialog box

15. Insert Formulas with Functions - continued • At Function Arguments palette, enter desired data. Function Arguments palette

16. Insert Formulas with Functions - continued • Excel performs over 300 functions that are divided into thirteen different categories including Financial, Date & Time, Math & Trig, Statistical, Lookup & Reference, Database, Text, Logical, Information, Engineering, Cube, Compatibility, and Web.

17. Insert Formulas with Functions - continued • Excel includes the Formula AutoComplete feature that displays a drop-down list of functions. AutoComplete list

18. Write Formulas with Statistical Functions • The AVERAGE function returns the average (arithmetic mean) of the arguments. AVERAGE function

19. Write Formulas with Statistical Functions - continued • The MAX function in a formula returns the largest value in a cell range. The MIN function returns the smallest value in a cell range. MIN function

20. Write Formulas with Statistical Functions - continued • Use the COUNT function to count the number of cells that contain numbers within the list of arguments. COUNT function

21. CHECKPOINT 1 • When writing your own formula, begin the formula with this. • division sign • minus sign • plus sign • equals sign • A function operates on what is referred to as this. • a cell • a selection • a range • an argument Answer Answer Next Question Next Question • If you want to change the order of operations, use these around the part of the formula that you want calculated first. • minus signs • equals signs • parentheses • plus signs • Use this function to count the numeric values in a range. • ADD • TOTAL • SUM • COUNT Answer Answer Next Question Next Slide

22. Write Formulas with NOW and TODAY Functions • The NOW and TODAY functions are part of the Date & Time category of functions. • The NOW function returns the current date and time in a date and time format. • The TODAY functionreturns the current date in a date format. Date & Time button

23. Display Formulas • In some situations, you may need to display the formulas in a worksheet rather than the results of the formula. • Display all formulas in a worksheet, rather than the results, by clicking the FORMULAS tab and then clicking the Show Formulas button in the Formula Auditing group. • You can also turn on the display of formulas with the keyboard shortcut Ctrl + `. • Press Ctrl + ` to turn off the display of formulas or click the Show Formula button on the FORMULAS tab.

24. Use Absolute and Mixed Cell References • A reference identifies a cell or a range of cells in a worksheet and can be relative, absolute, or mixed. • A relative cell reference refers to cells relative to a position in a formula. • An absolute cell reference refers to cells in a specific location. • A mixed cell reference does both: either the column remains absolute and the row is relative or the column is relative and the row remains absolute. • Distinguish between relative, absolute, and mixed cell references using the dollar sign ($).

25. Use Absolute and Mixed Cell References - continued • In some situations, you may want a formula to contain an absolute cell reference, which always refers to a cell in a specific location. absolute cell reference

26. Use Absolute and Mixed Cell References - continued • In a mixed cell reference, either the column remains absolute and the row is relative or the column is relative and the row is absolute. mixed cell reference

27. CHECKPOINT 2 • Display all formulas in a worksheet rather than the results by pressing these keys. • Ctrl + \ • Ctrl + ` • Ctrl + - • Ctrl + = • This function returns the serial number of the current date and time. • MIN • MAX • DATE • NOW Answer Answer Next Question Next Question • This function in a formula returns the largest value in a cell range. • MIN • COUNT • MAX • AVERAGE • This type of reference always refers to a cell in a specific location. • standard • default • relative • absolute Answer Answer Next Question Next Slide

28. Inserting Formulas in a Worksheet Summary of Presentation Concepts • Write formulas with mathematical operators • Type a formula in the Formula bar • Copy a formula • Use the Insert Function feature to insert a formula in a cell • Write formulas with the AVERAGE, MAX, MIN, COUNT, NOW, and TODAY functions • Create an absolute and mixed cell reference