1 / 17

Functions

Functions. BUS 782. What are functions?. Functions are prewritten formulas. We use functions to perform calculations. Enclose arguments within parentheses. Arguments are values on which you want to perform the calculation. Example: PMT function. Types of Functions.

orsen
Download Presentation

Functions

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. Functions BUS 782

  2. What are functions? • Functions are prewritten formulas. We use functions to perform calculations. • Enclose arguments within parentheses. Arguments are values on which you want to perform the calculation. • Example: PMT function

  3. Types of Functions • Built-in functions: Formulas/Insert function • Financial • Date & time • Math & statistical • Database • Lookup • Logical • Text • Etc.

  4. Math Functions • Quotient: Quotient(Dividend, Divisor) • Returns the integer portion of a division. • Example Quotient(17,5) • Remainder function: • MOD(Dividend, Divisor) • Returns the remainder after dividend is divided by divisor. • Example: MOD(17,5) • Note: Remainder = Dividend – Quotient * Divisor

  5. Examples of Using Quotient and Mod Functions

  6. Return the Smallest Number of Coins Examples: 26 cents: 1 Q, 1 P 57 cents: 2 Q, 1 N, 2 P 63 cents: 2 Q, 1 D, 3 P

  7. Rand and RandBetween • Rand() • Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated or by pressing the F9 key. • Example: Randomly selects 20% of students as samples • Randbetween(bottom, top) • Returns a random integer number between the numbers you specify.

  8. Statistical Functions • Sum, Average, Max, Min, Count • Ignore text and logical values. • Can have many arguments: • =SUM(A1:A3, 12, B3:B7)

  9. Running Total

  10. Examples of Using Statistical Functions Compute each student’s: Best score, Lowest score, exam average Average of the best 3 scores Average of the best 2 scores

  11. Large(Data range, kth value) • Returns the k-th largest value in a range. • Average of the best two exams: • =(Large(B2:E2,1) + Large(B2:E2,2))/2

  12. Average Excluding the Highest and the lowest Scores

  13. Lower(text): Lower(“David”) -> “david” Upper(text): Upper(“David”) -> “DAVID” Proper(text): The first letter in each word in uppercase and other in lower case Text Functions

  14. Date & Time Functions • How Excel handles dates: • Serial #: 1/1/1900 – day 1 • Functions: • Today() – today’s date • Now() – current date and time • Year(a date) • Month(a date) • Weekday(a date)

  15. Examples • Tomorrow’s date? • How many days to Christmas? • Compute the age given a date of birth.

  16. Financial Functions • PV, NPV, FV • IRR • PMT, IPMT, PPMT,CUMIPMT

More Related