1 / 59

MS Excel 2003

MS Excel 2003. Formulas and Functions Saima Gul. Purpose.

antionette-frankie
Télécharger la présentation

MS Excel 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MS Excel 2003 Formulas and Functions Saima Gul

  2. Purpose • One important task you can perform in Excel is to calculate totals for the values in a series of related cells. You can also use Excel to find out other information about the data you select, such as the maximum or minimum value in a group of cells. • Regardless of your bookkeeping needs, Excel gives you the ability to find the information you want. And if you should make an error, you can find the cause and correct it quickly. • Excel makes it easy to reference a number of cells at once, letting you define your calculations quickly.

  3. Naming Groups of Data • When you work with large amounts of data, it’s easier to identify groups of cells that contain related data. • Rather than specify the cells individually every time you want to use the data they contain, you can define those cells as a range (also called a named range). Whenever you want to use the contents of that range in a calculation, you can simply use the name of the range instead of specifying each cell individually.

  4. To name a range of cells • Select the cells to be included in the range. • Click in the Name box. • Type the name of the range, and press Enter.

  5. Creating Formulas to Calculate Values • Once you’ve added your data to a worksheet and defined ranges to simplify data references, you can create a formula, or an expression that performs calculations on your data. • To write an Excel formula, you begin the cell’s contents with an equal sign—when Excel sees it, it knows that the expression following it should be interpreted as a calculation and not text. After the equal sign, you type the formula. • For instance, you can find the sum of the numbers in cells C2 and C3 using the formula =C2+C3. • After you have entered a formula into a cell, you can revise it by clicking the cell and then editing the formula in the formula bar.

  6. Formulas Contd. • To create a new calculation, you click Function on the Insert menu. The Insert Function dialog box appears, with a list of functions, or predefined formulas, from which you can choose.

  7. Finding and Correcting Errors in Calculations • Excel makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing. • Excel identifies errors in several ways. The first way is to fill the cell holding the formula generating the error with an error code. • When a cell with an erroneous formula is the active cell, an Error button appears next to it. You can click the button’s down arrow to display a menu with options that provide information about the error and offer to help you fix it.

  8. Error Codes

  9. Entering and Editing Formulas • Entering a new formula into a worksheet appears to be a straightforward process: • Select the cell in which you want to enter the formula. • Type an equals sign (=) to tell Excel that you’re entering a formula. • Type the formula’s operands and operators. • Press Enter to confirm the formula. • Excel divides formulas into four groups: arithmetic, comparison, text, and reference.

  10. Using Arithmetic Formulas • Arithmetic formulas are by far the most common type of formula. They combine numbers, cell addresses, and function results with mathematical operators to perform calculations. • It consists of operators like addition (+), subtraction (-), multiplication (*), division (/), percentage (%), exponentiation (^).

  11. Using Comparison Formulas • A comparison formula is a statement that compares two or more numbers, text strings, cell contents, or function results. • If the statement is true, the result of the formula is given the logical value TRUE (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value FALSE (which is equivalent to 0)

  12. Using Text Formulas • A text formula is a formula that returns text. • Text formulas use the ampersand (&) operator to work with text cells, text strings enclosed in quotation marks, and text function results. • One way to use text formulas is to concatenate text strings. For example, if you enter the formula =“soft"&"ware" into a cell, Excel displays software. • You also can use & to combine cells that contain text. For example, if A1 contains the text Ben and A2 contains Jerry, entering the formula =A1&" and " &A2 returns Ben and Jerry.

  13. Using Reference Formulas • The reference operators combine two cell references or ranges to create a single joint reference.

  14. Operator Precedence • 3 ^ (15/5) * 2 – 5 • 3 ^ ((15/5) * 2 – 5) • 3 ^ (15 / (5 * 2 – 5 ))

  15. Understanding Relative Reference Format • When you use a cell reference in a formula, Excel looks at the cell address relative to the location of the formula. • For example, suppose that you have the formula =A1*2 in cell A3. To Excel, this formula says, “Multiply the contents of the cell two rows above this one by 2.” This is called the relative reference format, and it’s the default format for Excel. • This means that if you copy this formula to cell A4, the relative reference is still “Multiply the contents of the cell two rows above this one by 2,” but the formula changes to =A2*2 because A2 is two rows above A4. • This way of handling copy operations will save you incredible amounts of time when you’re building your worksheet models.

  16. Understanding Absolute Reference Format • When you refer to a cell in a formula using the absolute reference format, Excel uses the physical address of the cell. • You tell the program that you want to use an absolute reference by placing dollar signs ($) before the row and column of the cell address. • Talking about the old example, Excel interprets the formula =$A$1*2 as “Multiply the contents of cell A1 by 2.” • No matter where you copy or move this formula, the cell reference doesn’t change. The cell address is said to be anchored.

  17. Naming Formulas • Follow these steps to name a formula: • Choose Insert, Name, Define Name to display the New Name dialog box. • Enter the name you want to use for the formula in the Name text box. • In the Refers To box, enter the formula exactly as you would if you were entering it in a worksheet. • Click OK.

  18. Functions • Excel has various function categories, including the following: • Text • Logical • Information • Lookup and reference • Date and time • Math and trigonometry • Statistical • Financial • Database and table

  19. Typing a Function into a Formula • Whether you use a function on its own or as part of a larger formula, here are a few rules and guidelines to follow: • You can enter the function name in either uppercase or lowercase letters. Excel always converts function names to uppercase. • Always enclose function arguments in parentheses. • Always separate multiple arguments with commas. (You might want to add a space after each comma to make the function more readable. Excel ignores the extra spaces.) • You can use a function as an argument for another function. This is called nesting functions. For example, the function AVERAGE(SUM(A1:A10), SUM(B1:B15)) sums two columns of numbers and returns the average of the two sums

  20. Using the IF() Function (The Simplest Case) • Let’s start with the simplest version of the IF() function: • IF(logical_test, value_if_true) • logical_test: A logical expression—that is, an expression that returns TRUE or FALSE (or their equivalent numeric values: 0 for FALSE and any other number for TRUE). • value_if_true: The value returned by the function if logical_test evaluates to TRUE. • For example, consider the following formula: =IF(A1 >= 1000, “It’s big!”) • Another common use for the simple IF() test is to flag values that meet a specific condition. • For example, suppose you have a worksheet that shows the percentage increase or decrease in the sales of a long list of products. It would be useful to be able to flag just those products that had a sales decrease. A basic formula for doing this would look something like this: =IF(cell < 0, flag) • Here, cell is the cell you want to test, and flag is some sort of text that you use to point out a negative value. Here’s an example: =IF(B2 < 0, “<<<<<”)

  21. IF() Function (Handling a FALSE Result) • IF(logical_test, value_if_true, value_if_false) • logical_test: A logical expression. • value_if_true: The value returned by the function if logical_test evaluates to TRUE. • value_if_false: The value returned by the function if logical_test evaluates to FALSE. • For example, consider the following formula: =IF(A1 >= 1000, “It’s big!”, “It’s not big!”)

  22. IF() Function (Avoiding Division by Zero) • Excel displays the #DIV/0! error if a formula tries to divide a quantity by zero. To avoid this error, you can use IF() to test the divisor and ensure that it’s nonzero before performing your division. • For example, the basic equation for calculating gross margin is (Sales – Expenses)/Sales. To make sure that Sales isn’t zero, use the following formula: • =IF(Sales <> 0, (Sales - Expenses)/Sales, “Sales are zero!”)

  23. Performing Multiple Logical Tests • Excel offers several techniques for performing two or more logical tests: nesting IF() functions, the AND() function, and the OR() function.

  24. Nesting IF() Functions • When building models using IF(), it’s common to come upon a second fork in the road when evaluating either the value_if_true or value_if_false arguments. • For example, consider the variation of our formula that outputs a description based on the value in cell A1: =IF(A1 >= 1000, “Big!”, “Not big”) • What if you want to return a different string for values greater than, say, 10,000? In other words, if the condition A1 > 1000 proves to be true, you want to run another test that checks to see if A1 > 10000. • You can handle this scenario by nesting a second IF() function inside the first as the value_if_true argument: =IF(A1 >= 1000, IF(A1 >= 10000, “Really big!!”, “Big!”), “Not big”) • For example, if you want to return the description Small for a cell value less than 100, you would use this version of the formula: • =IF(A1 >= 1000, “Big!”, IF(A1 < 100, “Small”, “Not big”))

  25. The AND() Function • It’s often necessary to perform an action if and only if two conditions are true. • The AND() result is calculated as follows: • If all the arguments return TRUE (or any nonzero number), AND() returns TRUE. • If one or more of the arguments return FALSE (or 0), AND() returns FALSE. • You can use the AND() function anywhere you would use a logical formula, but it’s most often pressed into service as the logical condition in an IF() function. • In other words, if all the logical conditions in the AND() function are TRUE, IF() returns its value_if_true result; if one or more of the logical conditions in the AND() function are FALSE, IF() returns its value_if_false result. Here’s an example: • =IF(AND(B2 > 0, C2 > 0), “1000”, “No bonus”)

  26. The OR() Function • The OR() result is calculated as follows: • If one or more of the arguments return TRUE (or any nonzero number), OR() returns TRUE. • If all of the arguments return FALSE (or 0), OR() returns FALSE. • As with AND(), you use OR() wherever a logical expression is called for, most often within an IF() function. • This means that if one or more of the logical conditions in the OR() function are TRUE, IF() returns its value_if_true result; if all of the logical conditions in the OR() function are FALSE, IF() returns its value_if_false result. • Here’s an example: =IF(OR(B2 > 0, C2 > 0), “1000”, “No bonus”)

  27. Working with Lookup Functions • In many worksheet formulas, the value of one argument often depends on the value of another. Here are some examples: • In a formula that calculates an invoice total, the customer’s discount might depend on the number of units purchased. • In a formula that charges interest on overdue accounts, the interest percentage might depend on the number of days each invoice is overdue. • The usual way to handle these kinds of problems is to look up the appropriate value.

  28. Understanding Lookup Tables • The table—more properly referred to as a lookup table—is the key to performing lookup operations in Excel. • The most straightforward lookup table structure is one that consists of two columns (or two rows): • Lookup column—This column contains the values that you look up. For example, if you were constructing a lookup table for a dictionary, this column would contain the words. • Data column—This column contains the data associated with each lookup value. In the dictionary example, this column would contain the definitions. • In most lookup operations, you supply a value that the function locates in the designated lookup column. It then retrieves the corresponding value in the data column.

  29. The CHOOSE() Function • The simplest of the lookup functions is CHOOSE(), which enables you to select a value from a list. Specifically, given an integer n, CHOOSE() returns the nth item from the list. Here’s the function’s syntax: • CHOOSE(num, value1[, value2,...]) • Num: Determines which of the values in the list is returned. If num is 1, value1 is returned; if num is 2, value2 is returned (and so on). num must be an integer (or a formula or function that returns an integer) between 1 and 29. • value1, value2...: The list of up to 29 values from which CHOOSE(selects the return value. The values can be numbers, strings, references, names, formulas, or functions. • For example, consider the following formula: =CHOOSE(2,”Surface Mail”, “Air Mail”, “Courier”) • The num argument is 2, so CHOOSE() returns the second value in the list, which is the string value Air Mail.

  30. Looking Up Values in Tables • CHOOSE() does have its drawbacks: • The lookup values must be positive integers. • The maximum number of data values is 29. • Only one set of data values is allowed per function. • Excel can use a wider variety of lookup values (negative or real numbers, strings, and so on), and it can also accommodate multiple data sets that each can have any number of values (subject, of course, to the worksheet’s inherent size limitations). • Excel has two functions that meet these criteria: VLOOKUP() and HLOOKUP().

  31. The VLOOKUP() Function • The VLOOKUP() function works by looking in the first column of a table for the value you specify. (The V in VLOOKUP() stands for vertical.) • It then looks across the appropriate number of columns (which you specify) and returns whatever value it finds there. • Here’s the full syntax for VLOOKUP(): • VLOOKUP(lookup_value, table_array, col_index_num[, range_lookup]) • lookup_value: This is the value you want to find in the first column of table_array. You can enter a number, string, or reference. • table_array: This is the table to use for the lookup. You can use a range reference or a name. • col_index_num: If VLOOKUP() finds a match, col_index_num is the column number in the table that contains the data you want returned (the first column—that is, the lookup column—is 1, the second column is 2, and so on). • range_lookup: This is a Boolean value that determines how Excel searches for lookup_value in the first column: TRUE—VLOOKUP() searches for the first exact match for lookup_value. If no exact match is found, the function looks for the largest value that is less than lookup_value (this is the default). FALSE—VLOOKUP() searches only for the first exact match for lookup_value.

  32. The VLOOKUP() Function (Contd.) • Here are some notes to keep in mind when you work with VLOOKUP(): • If range_lookup is TRUE or omitted, you must sort the values in the first column in ascending order. • If the first column of the table is text, you can use the standard wildcard characters in the lookup_value argument (use ? to substitute for individual characters; use * to substitute for multiple characters). • If lookup_value is less than any value in the lookup column, VLOOKUP() returns the #N/A error value. • If VLOOKUP() doesn’t find a match in the lookup column, it returns #N/A. • If col_index_num is less than 1, VLOOKUP() returns #VALUE!; if col_index_num is greater than the number of columns in table, VLOOKUP() returns #REF!.

  33. The HLOOKUP() Function • The HLOOKUP() function is similar to VLOOKUP(), except that it searches for the lookup value in the first row of a table. (The H in HLOOKUP() stands for horizontal.) • If successful, this function then looks down the specified number of rows and returns the value it finds there. • Here’s the syntax for HLOOKUP(): • HLOOKUP(lookup_value, table_array, row_index_num[, range_lookup]) • lookup_value: This is the value you want to find in the first row of table_array. You can enter a number, string, or reference. • table_array: This is the table to use for the lookup. You can use a range reference or a name. • row_index_num: If HLOOKUP() finds a match, row_index_num is the row number in the table that contains the data you want returned (the first row—that is, the lookup row—is 1, the second row is 2, and so on). • range_lookup: This is a Boolean value that determines how Excel searches for lookup_value in the first row: TRUE—VLOOKUP() searches for the first exact matchfor lookup_value. If no exact match is found, the function looks for the largest value that is less than lookup_value (this is the default). FALSE—VLOOKUP() searches only for the first exact match for lookup_value.

  34. Date and Time Functions

  35. Returning a Date TODAY():Returning the Current Date • Excel offers three functions that can help: TODAY(), DATE(), and DATEVALUE(). • When you need to use the current date in a formula, function, or expression, use the TODAY() function, which doesn’t take any arguments: • TODAY() • This function returns the serial number of the current date, with midnight as the assumed time.

  36. DATE():Returning Any Date • A date consists of three components: the year, month, and day. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper date out of them. You can do that by using Excel’s DATE() function: • DATE(year, month, day) • Year: The year component of the date (a number between 1900 and 9999) • Month: The month component of the date • Day: The day component of the date • For example, the following expression returns the serial number of Christmas Day in 2007: • DATE(2007, 12, 25)

  37. DATEVALUE():Converting a String to a Date • If you have a date value in string form, you can convert it to a date serial number by using the DATEVALUE() function: • DATEVALUE(date_text) • date_text: The string containing the date • For example, the following expression returns the date serial number for the string August 23, 2007: • DATEVALUE(“August 23, 2007”)

  38. Returning Parts of a Date • A date’s components are extracted using Excel’s YEAR(), MONTH(), and DAY() functions. The YEAR() Function • The YEAR() function returns a four-digit number that corresponds to the year component of a specified date: • YEAR(serial_number) • serial_number: The date (or a string representation of the date) you want to work with • For example, if today is August 23, 2007, the following expression will return 2007: • YEAR(TODAY()) • The MONTH() and the DAY() functions work in a similar fashion.

  39. Determining a Person’s Birthday Given the Birth Date • If you know a person’s birth date, determining that person’s birthday is easy: Just keep the month and day the same, and substitute the current year for the year of birth. • To accomplish this in a formula, you could use the following: • =DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) • Birthdate is the heading of the column.

  40. Returning a Time • For returning time, Excel provides following functions: NOW(), TIME(), and TIMEVALUE(). NOW():Returning the Current Time • When you need to use the current time in a formula, function, or expression, use the NOW()function, which doesn’t take any arguments: • NOW()

  41. TIME():Returning Any Time • A time consists of three components: the hour, minute, and second. • It often happens that a worksheet generates one or more of these components and you need some way of building a proper time out of them. You can do that by using Excel’s TIME() function: • TIME(hour, minute, second) • hour: The hour component of the time (a number between 0 and 23) • minute: The minute component of the time (a number between 0 and 59) • second: The second component of the time (a number between 0 and 59) • For example, the following expression returns the serial number of the time 2:45:30 p.m.: • TIME(14, 45, 30)

  42. TIMEVALUE():Converting a String to a Time • If you have a time value in string form, you can convert it to a time serial number by using the TIMEVALUE() function: • TIMEVALUE(time_text) • time_text: The string containing the time • For example, the following expression returns the time serial number for the string 2:45:00 PM: • TIMEVALUE(“2:45:00 PM”)

  43. Returning Parts of a Time • The three components of a time—hour, minute, and second—can also be extracted individually from a given time using Excel’s HOUR(), MINUTE(), and SECOND() functions. The HOUR() Function • The HOUR() function returns a number between 0 and 23 that corresponds to the hour component of a specified time: • HOUR(serial_number) • serial_number: The time (or a string representation of the time) you want to work with • For example, the following expression returns 12: • HOUR(0.5) • Similar are MINUTE() and SECOND() functions.

  44. Math & Statistical Functions

  45. The ROUND() Function • The rounding function you’ll use most often is ROUND(): • ROUND(number, num_digits) • number: The number you want to round • num_digits: An integer that specifies the number of digits you want number rounded to, as explained here:

  46. The EVEN() and ODD() Functions • The EVEN() and ODD() functions round a single numeric argument: • EVEN(number) ODD(number) • number: The number you want to round • Both functions round the value given by number away from 0, as follows: • EVEN() rounds to the next even number. For example, EVEN(14.2) returns 16, and EVEN(–23) returns –24. • ODD() rounds to the next odd number. For example, ODD(58.1) returns 59 and ODD(–6) returns –7.

  47. The INT() and TRUNC() Functions • The INT() and TRUNC() functions are similar in that you can use both to convert a value to its integer portion: • INT(number) • TRUNC(number[, num_digits]) • number: The number you want to round • num_digits: An integer that specifies the number of digits you want number rounded to, as explained here: • For example, INT(6.75) returns 6, and TRUNC(3.6) returns 3. However, these functions have two major differences that you should keep in mind: • For negative values, INT() returns the next number away from 0. For example, INT(–3.42) returns –4. If you just want to lop off the decimal part, you need to use TRUNC() instead. • You can use the TRUNC() function’s second argument—num_digits—to specify the number of decimal places to leave on. For example, TRUNC(123.456, 2) returns 123.45, and TRUNC(123.456, –2) returns 100.

  48. The SUM() Function • Here’s the syntax of the SUM() function: • SUM(number1[, number2, ...]) • number1, number2,... The values you want to add • For example, the following formula returns the sum of the values in three separate ranges: • =SUM(A2:A13, C2:C13, E2:E13) The MOD() Function • The MOD() function calculates the remainder (or modulus) that results after dividing one number into another. Here’s the syntax for this more-useful-than-you-think function: • MOD(number, divisor) • number: The dividend (that is, the number to be divided) • divisor: The number by which you want to divide number • For example, MOD(24, 10) equals 4 (that is, 24 •10 = 2, with remainder 4)

  49. Counting Items with the COUNT() Function • The simplest of thedescriptive statistics is the total number of values, which is given by the COUNT() function: • COUNT(value1[,value2,...]) • value1, value2,... One or more ranges, function results, expressions, or literal values of which you want the count

  50. The AVERAGE() Function • The mean is what you probably think of when someone uses the term average. That is, it’s the arithmetic mean of a set of numbers. • In Excel, you calculate the mean using the AVERAGE() function: • AVERAGE(number1[,number2,...]) • number1, number2,... A range, or list of values of which you want the mean The MEDIAN() Function • The median is the value in a data set that falls in the middle when all the values are sorted in numeric order. That is, 50% of the values fall below the median, and 50% fall above it. • The median is useful in data sets that have one or two extreme values that can throw off the mean result because the median is not affected by extremes. • You calculate the median using the MEDIAN() function: • MEDIAN(number1[,number2,...]) • number1, number2,... A range, or list of values of which you want the median

More Related