Microsoft Excel Lessons on functions and charts (1)

Lesson 1

Numbers and Mathematical Calculations

Microsoft Excel has many functions that you can use. Functions allow you to quickly and easily find an average, the highest number, the lowest number, a count of the number of items in a list, and make many other useful calculations.

Formula

·         Formulas are used to compute values.

·         An equal sign must precede a formula.

·         Formulas may be typed in lower or uppercase letters; you may type a space between operators.

Function

• A prewritten formula that provides a quick means of performing a calculation.

• Common functions include:  Average, Sum, Maximize, Minimize.

• The AutoSum button may be used to sum numbers.  To do so, click the AutoSum button once to display formula, then click again to display the value.

·     Excel built-in functions enable the user to easily create formula that involves several cells or group of cells.

Reference Operators

Reference operators refer to a cell or a group of cells. There are two types of reference operators, range  and union.
A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon. The reference A1:A3 includes cells A1, A2, and A3. The reference A1:C3 includes A1, A2, A3, B1, B2, B3, C1, C2, and C3.
A union reference includes two or more references. A union reference consists of two or more cell addresses separated by a comma. The reference A7,B8,C9 refers to cells A7, B8, and C9.

Functions

Microsoft Excel has a set of prewritten formulas called functions. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. When using a function, remember the following:
        Use an equal sign to begin a formula.
        Specify the function name.
        Enclose arguments within parentheses.
        Use a comma to separate arguments.

Lesson 2

Formatting Makes the Displayed Result Incorrect

          If the result that appears in a cell is obviously incorrect but the underlying formula seems to be correct, check that the cell’s formatting isn’t forcing Excel to round the result for the display.

####	The formula is fine, but the cell is too narrow to	Widen the column.
	display the formula result.
#NAME?	The formula contains a misspelled function	If the problem is a function name or a
	name or the name of a nonexistent range.	misspelled range name, correct it. If
		you’ve deleted a range name, define it again.
#N/A	No valid value is available.	Enter a valid value if necessary.
#REF!	The formula contains an invalid cell reference or	Change the formula to remove the
	range reference. For example, you may have	invalid reference.
	deleted a cell or range that the formula needs.
#DIV/0!	The formula is attempting to divide by zero.	If the divisor value is actually 0,
		change it. If a blank cell is producing
		the 0 value, add an IF() statement to
		supply the #N/A value or a usable value
#VALUE!	The formula contains an invalid argument—for	Correct the argument or change the
	example, text instead of a number.	formula.
#NULL!	The specified two ranges have no intersection.	Correct one or both ranges so that they intersect.
#NUM!	The number specified isn’t valid for the function	Correct the number to suit the function.
	or formula. For example, using a POWER
	function has generated a number larger than
	Excel can handle, or SQRT (the square root
	function) has been fed a negative number.

 

Lesson 3

Understanding Functions

          Excel includes a large number of functions—built-in, predefined formulas for standard calculations. Excel’s functions range from the everyday to the highly specialized.

Understand the Components of a Function

            Each function has a name entered in capitals and followed by a pair of parentheses—for example, SUM(), MAX(), or DATEVALUE(). Almost all functions have one or more arguments, which specify the elements and types of information you give them in order to get a valid result.

          The rules that govern the types of information a function needs are called its syntax. Excel shows required arguments in boldface, optional arguments in regular font, and an ellipsis to indicate where you can use further arguments of the same type.

          For example, the syntax for the =SUM() function is

SUM(number1,number2,...)

Here, number1 is a required argument that specifies the first number to include in the sum: you can’t have a SUM without a number. The number2 argument is an optional argument that specifies the second number, if there is one. The ellipsis indicates that you can use further arguments— number3, number4, and so on—as necessary.

Enter Functions in Worksheets

          You can enter a function in the active cell in three ways:

1.   Type the function directly into the cell.

2.   Use the AutoSum button and drop-down menu on the Standard toolbar.

3.   Use the Insert Function dialog box.

          The following sections discuss these ways of entering a function and explain when to use each method.

Type a Function Directly into a Cell

         

          The most straightforward way to enter a function is to type it and its arguments directly into the cell. When you’ve typed enough to identify the function you’re entering, Excel displays a ScreenTip that shows the syntax for the function and tracks your progress in entering the argument.

Use the AutoSum Drop-Down Menu

           

            The other quick way to enter a function is to use one of the frequently used functions on the AutoSum button and drop-down menu on the Standard toolbar. The AutoSum button itself inserts the SUM() function. The AutoSum drop-down menu contains entries for the Average, Count, Max, and Min functions, together with a More Functions entry that displays the Insert Function dialog box (discussed in the “Use the Insert Function Dialog Box” section, next).  

Use the Insert Function Dialog Box

          The third way of entering a function is by using the Insert Function dialog box, which walks you through the process of choosing a function and specifying its arguments correctly. This dialog box is the fastest and easiest method for entering all but the most basic functions—those functions that appear on the AutoSum drop-down menu or those functions that take no arguments.