1 / 80

Chapter 4: Functions and Formulas

Spreadsheet-Based Decision Support Systems. Chapter 4: Functions and Formulas. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 4.1 Introduction 4.2 Formulas and Function Categories

lamar
Télécharger la présentation

Chapter 4: Functions and Formulas

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. Spreadsheet-Based Decision Support Systems Chapter 4: Functions and Formulas Prof. Name name@email.com Position (123) 456-7890 University Name

  2. Overview • 4.1 Introduction • 4.2 Formulas and Function Categories • 4.3 Logical and Information Functions • 4.4 Text and Lookup & Reference Functions • 4.5 Date & Time Functions • 4.6 Mathematical and Trigonometry Functions • 4.7 Statistical and Financial Functions • 4.8 Conditional Formatting Formulas • 4.9 Auditing • 4.10 Summary

  3. Introduction • Various function names, arguments, and examples • Excel’s function dialog boxes • Formula Is option in conditional formatting • Auditing formulas and functions

  4. Formulas and Function Categories • Formulas • Simple values • Basic operators • Naming and referencing • Functions • Function Categories • Logical • Information • Text and • Lookup & Reference • Date & Time • Math & Trig • Statistical • Financial

  5. Basic Operators • Basic operators perform simple calculator functions such as addition, subtraction, multiplication, and division with numbers in cells. • These mathematical functions used in a cell follow the order of operations. • To do this, we type an “=” followed by the equation into the cell.

  6. Finding and Using Functions • Function Library group in the Formulas tab of the Ribbon lists a number of function categories: • Financial, Logical, Text, Date and Time, Lookup & Reference, and Mathematics and Trigonometry • A list of functions are available for use within each category by selecting the corresponding command from the Function Library group.

  7. Finding and Using Functions • To display the Insert Function dialog box, we click on the Insert Function fxicon located both, in the Function Library group, and in the Formula Bar.

  8. Function Arguments • Once we select a function, the Function Arguments dialog box appears. • Almost every Function has at least one argument, or parameter. • The Help on this Function command in the bottom-left corner of these dialog boxes gives a detailed explanation of the function as well as an example of how to use it.

  9. Finding and Using Functions (cont’d) • The Formulas tab > Function Library group > Recently Used command displays approximately the last ten functions used. • The Formulas tab > Function Library group > More Functions command lists additional function categories such as, Statistical, Engineering, Cube, Information and Compatibility. • Formula Autocomplete is a useful feature that displays a menu of all functions that begin with the specified letters.

  10. Figure 4.7(a) • The SUM function sums the values in any given range of values • =SUM(number1, number2) or =SUM(range name)

  11. Figure 4.7(b) • The AVERAGE function finds the average of a given range • =AVERAGE(number1, number2) or =AVERAGE(range name)

  12. Figure 4.7(c) • The MIN and MAX functions find the minimum or maximum value in a given range of values • =MIN(number1, number2) or =MIN(range name) • =MAX(number1, number2) or =MAX(range name)

  13. Logical Functions • TRUE and FALSE • NOT • IF and IFERROR • AND and OR

  14. Figures 4.9 and 4.10 • The TRUE and FALSE functions simply display the words TRUE and FALSE respectively (no parameters) • =TRUE or =FALSE • The NOT function is used to display the opposite of any of the results of the other Logical functions • =NOT(cell_name)

  15. Figure 4.11 • The IF function uses a specified condition to determine whether your data is true or false, and then returns a user-specified result in each case. • =IF(logical_test, value_if_true, value_if_false) • The IFERROR function returns a user-specified value if the formula gives an error, and the value of the formula itself otherwise. • =IFERROR(value, value_if_error)

  16. Figure 4.12(a) • The AND function evaluates a list of conditions as True or False • =AND(condition1, condition2, …) • All of the conditions must be true in order for TRUE to be displayed. • If any of the conditions are violated, FALSE will be returned

  17. Figure 4.12(b) • The OR function also evaluates a list of conditions • =OR(condition1, condition2, …) • Only one of the conditions needs to be true for TRUE to be the result • All of the conditions would have to be violated in order to return FALSE

  18. Information Functions • There are several different Information functions • All of these functions give some basic descriptive information about any given data • One group of these functions we call the IsFunctions

  19. Figure 4.14 • The ISEVEN and ISODD functions evaluate whether or not a number in a cell is an even or odd number, respectively. • =ISEVEN(cell_name) or =ISEVEN(number) • =ISODD(cell_name) or =ISODD(number)

  20. Figure 4.15 • The ISTEXT and ISNUMBER functions return TRUE or FALSE if a cell value is text or not, or a number or not, respectively. • =ISTEXT(cell_name) or =ISTEXT(value) • =ISNUMBER(cell_name) or =ISNUMBER(value)

  21. Figure 4.16 • The TYPE function evaluates the data type of a value • =TYPE(cell_name) or =TYPE(value) • a data type is a descriptive category of the different types of values possible in Excel • Excel has designated a particular number to reference the categories of each data type. • 1 = numerical • 2 = text • 4 = logical value

  22. Text Functions • Text functions manipulate text values or analyze their characteristics • There are several Text Functions. We will discuss: • UPPER and LOWER • CONCATENATE • SUBSTITUTE.

  23. Figure 4.18 • The UPPER and LOWER functions convert a cell, or range of cells, with text values into all uppercase or all lowercase text, respectively • =UPPER(range_name) or =LOWER(range_name)

  24. Figure 4.19 • The CONCATENATE function joins fragments of a phrase or sentence together by combining text values of multiple cell • =CONCATENATE(cell1, cell2, …)

  25. Figure 4.20 • The SUBSTITUTE function takes a cell with text and exchanges old text for new text. • =SUBSTITUTE(cell_name, old_text, new_text, instance)

  26. Lookup & Reference Functions • Lookup & Reference functions search for information within a given table of data and perform some actions on that data • There are several of these functions; for now, we describe: • VLOOKUP and HLOOKUP • MATCH • INDEX • OFFSET

  27. VLOOKUP and HLOOKUP • The VLOOKUP and HLOOKUP functions are helpful when searching for data in the spreadsheet • VLOOKUP searches for a value in the left-most column of a table, marks the row that contains that value, and then returns a value from that row for a specified column • =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

  28. VLOOKUP and HLOOKUP (cont’d) • HLOOKUP searches for a value in the top row of a table, marks the column that contains that value, and then returns a value from that column for a specified row • =HLOOKUP(lookup_value, table_array, row_index_number, range_lookup) • The range_lookup parameter measures the exactness for searching for the first parameter value. • True = find the closest match (default) • False = find an exact match

  29. Figure 4.21 • =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

  30. Figure 4.22 • =HLOOKUP(lookup_value, table_array, row_index_number, range_lookup)

  31. MATCH • The MATCH function searches a table of data and returns the location of a desired value. • =MATCH(lookup_value, table_array, match_type) • The match_typeparameter, can be 0, 1, or –1. • 0 = the location of the first value it finds that is equal to the value for which we are searching (default) • 1 = the location of the largest value that is less than or equal to our specified value (given that the data is in ascending order) • –1 = the location of the smallest value that is greater than or equal to our value (given that the data is in descending order

  32. Figure 4.23 • Searching a column for closest matches

  33. Figure 4.24 • Searching a row for an exact match

  34. Figure 4.25 • The INDEX function, like the MATCH function, allows us to find an entry in a specified row and column of a range of cells. • =INDEX(range or range_name, row_number, column_number) • Example: The table below stores the distances between ten US cities.

  35. Figure 4.26 • Use the INDEX function to compute the distance between US cities.

  36. Figure 4.27 • We can also use the INDEX function to refer to an entire row or an entire column.

  37. Figures 4.28 and 4.29 • The OFFSET function references a cell that is a given number of rows and columns from a specified cell, or range of cells. • =OFFSET(reference_cell, rows_to_move, columns_to_move, [height], [width]) • Use a table of numbers to demonstrate the use of the OFFSET function. • Name the cell C2 the “RefCell” since we will reference this cell most often.

  38. Figure 4.30 • The OFFSET function can also be useful in combination with other functions. • To find the sum of the values in the last column of the table, we use both the SUM and OFFSET functions.

  39. Date & Time Functions • Excel’s system for calculating dates and times uses a serial number to enumerate all dates and times • For dates, this number considers January 1, 1900 to be an initial starting point, which it sets to zero, and then counts each day thereafter as one unit • For time, the initial starting point is at zero hours, zero minutes, and zero seconds counting toward the current time on a 24-hour scale. (It is reset to zero at midnight of each day.) • It is by using this numerical system that we are able to perform the functions in this category.

  40. Date & Time Functions (cont’d) • There are several functions in this category; we describe: • TODAY and NOW • NETWORKDAYS, DAYS360, and YEARFRAC • WEEKDAY and MONTH • HOUR and MINUTE

  41. Two Functions • The TODAY and NOW functions display the current date and time, respectively (there are no parameters for these functions) • =TODAY() • =NOW()

  42. Three More Functions • NETWORKDAYS finds the number of workdays between two dates • DAYS360 finds the total number of days between two dates • YEARFRAC finds the fraction of a year between two dates • Let’s consider an example of a company that receives shipments of office supplies each month.

  43. Figure 4.34 • =NETWORKDAYS(start_date, end_date, holidays)

  44. Figure 4.35 • =DAYS360(start_date, end_date, method)

  45. Figure 4.36 • =YEARFRAC(start_date, end_date, basis)

  46. Four More Functions • The MONTH function determines to which month your date belongs. • The months are enumerated with January as 1 through December as 12. • The WEEKDAY function determines to which day of the week your date refers. There are three possible numbering methods: • 1 = Sunday as day 1 and Saturday as day 7 (default) • 2 = Monday as day 1 and Sunday as day 7 • 3 = Monday as day 0 and Sunday as 6 • The HOUR function takes the time and returns the number of the hour to which it belongs using a numbering system from 12:00 AM as 0 to 11:00 PM as 23. • The MINUTE function takes the time and returns a minute number from 0 to 59.

  47. Figure 4.38 • =WEEKDAY(date, method) or =WEEKDAY(cell_name, method)

  48. Figure 4.39 • =MONTH(date) or =MONTH(cell_name)

  49. Figure 4.40 • =HOUR(time) or =HOUR(cell_name) • =MINUTE(time) or =MINUTE(cell_name)

  50. Mathematical and Trigonometry Functions • Already used: • SUM • AVERAGE • MIN • MAX • We will now describe: • ABS • PRODUCT • SUMPRODUCT • MMULT • SQRT • PI • SIN, COS, and TAN

More Related