Download to read offline
Excel 2003 tutorial 3
- 1. Excel 2003 –Tutorial III Functions & Formulas Fundamentals Formula Definition Formula Syntax Formula Bar Function Definition Function Syntax Arguments Operators Operator Order Function Wizards Entering Multiple Formulas All At Once Editing & Deleting Formulas Errors in Formulas Excel Functions Overview Statistical Functions Overview Math Functions Overview Information Functions Overview Overview of Logical Functions Additional Readings Text to Speech Excel 2003 – Tutorial 3 1
- 2. Functions & FormulasFundamentals The following definitions are necessary to understand the basics of creating Excel formulas and functions. Formula Definition A formula allows you to calculate and analyze data in your worksheet. Formulas perform calculations such as addition or multiplication; formulas can also combine values. Formula Syntax Formula syntax is the structure or order of the formula elements. All formulas begin with an equal sign (=) in Excel followed by operands (the data to be calculated) and the operators. Operands can be values that don’t change (constants), a range reference, a label, a name, or a worksheet function. Formula Bar The Formula bar is an area located at the top of the worksheet window that is used to enter or edit values or formulas in cells or charts. The Formula bar displays the constant value or formula in the active cell. To display or hide the Formula bar; select from the Menu bar, View > Formula. Function Definition A function in Excel is a built-in formula that performs a mathematical operation or returns information specified by the formula. As with every formula created in Excel, each function starts with an equal (=) sign. Function Syntax The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, an equal sign (=) displays before the function name. Example: =SUM (D2:F8) Excel 2003 – Tutorial 3 2
- 3. In the aboveexample, the function name is Sum and the argument for the function is the range “D2:F8”. Arguments An argument is the reference behind the function. The reference can be any of the following type: Argument Types Argument Example Numbers 1,2,3 Text “January” Logical Values (True or False) Cell References / Range B7 or B7:B20 Operators Operators are mathematical symbols that are broken into four categories Keystroke Operators Arithmetic Explanation Example + Addition 2+3 - Subtraction 5-1 * Multiplication 7*3 / Division 8/2 % Percent 90% ^ Exponentiation 7^2 Comparison Explanation Example = Equal to B1=D1 > Greater than B1>D1 < Less than B1<D1 >= Greater than or equal to B1>=D1 <= Less than or equal to B1<=D1 <> Not equal to B1<>D1 Text Explanation Example & Adjoins text or cell references "Scott" & "Hi" produces "Scott Hi" Reference Explanation Example : Includes cells of a column or row B3 : B20 between the designated limits , Separates arguments in a function (B3, B20) Excel 2003 – Tutorial 3 3
- 4. Operator Order Formulas arecalculated left to right, using order of precedence, the parentheses have high order of precedence, i.e.: every thing inside them is evaluated first. Excel performs operations in the order shown in the following table. Excel's Operator Order Arithmetic Operator Precedence Example 1 % Percent 2 ^ Exponentiation 3 *, / Multiplication, Division 4 +,- Addition, Subtraction 5 & Ampersand 6 >,>=,<,<=,=,<> Comparisons Notice that percent has the highest precedence, multiplication and division have same order of precedence, also addition and subtraction have same order of precedence. Excel performs all operations within sets of parentheses first, and you can use this to get exactly the order of operations you want. If multiple operations are encased in multiple sets of parentheses, the operations are performed from inside to outside, then follow the order of operations, and then left to right. Function Wizard The function wizard is designed to help provide the necessary arguments and descriptions for the various Excel functions. 1. Select the cell in which you want the results of the function to display. 2. Click the Insert Function button on the Formula toolbar or select Function from the Insert menu. 3. From the Insert Function dialog box, browse through the functions by selecting a Function category from the drop-down menu, and select the function from the list below. As each function name is highlighted a description and example is provided below the two boxes. 4. Click OK to select a function Excel 2003 – Tutorial 3 4
- 5. 5. The nextwindow allows you to choose the cells that contain the arguments of the function. In this example, cell B2 and C2 are selected to compute their sum. The values of the cells B2, and C2 are respectively 2, and 3. Excel identifies the range of the cells in the function to (B2:C2). In the lower part of the Function Argument dialogue box you can see the Formula result. 6. Click the OK button. Entering Multiple Formulas All At Once To enter the same formula in several cells at once, follow these steps: 1. Select all the cells you want to enter the formula in. 2. Create your formula, but don’t press ENTER when finished. 3. When the formula is complete, press CTRL + ENTER. The formula is entered in all the selected cells simultaneously. Editing & Deleting Formulas You can also edit or delete any formula. To delete a formula, click on the cell that contains the formula, and press the DELETE key on your keyboard. If you need to alter the formula, follow these steps: 1. Click on the cell that contains the formula. 2. Click on the Formula Bar and make changes to your function. If the formula uses a built-in Excel function: 1. Click on the cell that contains the function to select it 2. Click on the Insert Function button on the formula bar to edit the function arguments. Change the appropriate argument(s) and click OK. Excel 2003 – Tutorial 3 5
- 6. Errors in Formulas Whena formula is prevented to run normally, Excel will notify you with an error message. Each error message helps users identify the problem they are facing. The following table lists common Excel errors that you might face. Error Values Error Meaning How to Fix #### The column is too narrow to display the Widen the column result of calculation # VALUE Wrong type of argument or reference Check operands and arguments #DIV/0! Data is attempting to divide by zero Change the value or the cell reference so that the formula doesn't divide by zero #NAME? Formula is referencing an invalid name Be sure the name still exists or correct the misspelling #REF! Excel can't locate the referenced cells(for Click Undo to restore example, the cells were deleted) references and then change formula references #NULL Reference to intersection of two areas that Check for typing and reference do not intersect errors Excel Functions Overview Statistical Functions Overview Statistical functions are among the most widely used functions in Excel. Function Function Description AVERAGE(range) Calculates the mean (arithmetic average) of a range of cells COUNT(range) Counts the number of values (cells containing numbers in a range) COUNTIF(range, value) Counts the number of cells that are the same as a specified value. MAX(range) Returns the maximum value of a data set. MIN(range) Returns the minimum value of a data set. MODE(range) Returns the most frequently occurring, or repetitive, value in a range of data. STDEV(range) Calculates the standard deviation of a sample. Given that: • Range: Represents the set of values (number1, number2…) Excel 2003 – Tutorial 3 6
- 7. • Value: Thecriteria upon which you want to evaluate; it can be a number (14), a cell reference (G5), an expression (E5>7), or text (“Victor”). Math Functions Overview Math functions in Excel can be used to perform calculations as stand-alone functions or combined to create complex formulas. Excel has a great number of Math functions but the most commonly used ones are: 1. Sum 2. Round 3. Ceiling 4. Floor You can use the Round ( ), Ceiling ( ), or Floor ( ) function to round a number to any number of digits you want. 1. Sum Function • Adds the numbers in a range or multiple ranges of cells. • Written as: Sum (number1, number2…) • Number1, Number2..: are written as a number, a reference to a cell, or to a range of cells for which you want the total value or sum. 2. Round Function • Rounds a number to a specified number of digits. • Written as: Round (number, num_digits) • Number: The value you want to round. • Num_digits: The number of decimal places you want to round. 3. Ceiling Function • Returns number rounded up, away from zero, to the nearest multiple you specify. • Written as: Ceiling (number, significance) • Number: The value you want to round • Significance: The multiple you want to round to 4. Floor Function • Rounds number down, toward zero, to the nearest multiple you specify. • Written as: Floor (number, significance) • Number: The value you want to round. • Significance: The multiple you want to round to Information Functions Overview Information functions are generally made up of logical results and can be used in many business situations. Combined with other functions, the information functions can Excel 2003 – Tutorial 3 7
- 8. manage lists ofdata and provide feedback based on a logical result. The most useful function is: IsNumber Function • Returns true if the value is a number. • Written as: ISNUMBER (value) • Value: this is the cell or range you want tested. Overview of Logical Functions Logical functions test cells & ranges and can return only: True or False. Commonly used logical functions are: 1. AND 2. OR 3. NOT 4. IF 1. AND Function • Returns True if all the logical arguments are true. • Returns False if one or more arguments is false. • Written as: AND (logical1, logical2 …) • Logical Value 1, 2 … : The test results in a logical TRUE or FALSE return. Up to 30 conditions can be tested together. 2. OR Function • Returns False if all the logical arguments are false • Returns True if one or more arguments is true • Written as: OR (logical1, logical2…) • Logical Value 1, 2 …: These are the conditions to be met to test a logical true or false result. You can use up to 30 conditions within the formula. 3. NOT Function • Returns the opposite of the logical value • Written as: NOT (logical) Logical : This is the value that can be evaluated with a True or False Condition. If True, NOT returns False, if False, NOT returns True. 4. IF Function • The IF ( ) function decides the contents of a cell on a spreadsheet based on whether a test condition is true or false. Excel 2003 – Tutorial 3 8
- 9. • It returns a value if one condition is True, and returns another value or result if the condition is False. • Written as: IF (logical_test, value_if: true, value_if_false) Logical_test : Is any value or expression that can be evaluated to True or False. VALUE IF TRUE : Is the value returned if the logical_test is True. VALUE IF FALSE : Is the value returned if the logical_test is False The operators in the logical_test of the IF function may be: = Equal to <> Not equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to Note: Nested IF ( ) Function If you wish to test more than one condition, you can nest an if () function within another =if (logical_test, result_if_true, if (logical_test, result_if_true, if (logical_test, result_if_true, result if false)) Example: If (I am married, If (I get a child, I will send him/her to AUB)) Additional Readings Text to Speech To hear the text in your worksheet: 1. Show the Text to Speech toolbar by selecting from the Menu bar, View > Toolbars > Text to Speech 2. Highlight the cells you want to convert to speech 3. Press on speak cells button found on the Text to Speech toolbar ****************************** Excel 2003 – Tutorial 3 9