Functions
Functions in Excel are prewritten formulas that simplify calculations. By enclosing arguments in parentheses, you can perform various calculations efficiently. Excel includes built-in functions such as financial, date & time, mathematical, logical, and text functions. Key functions like PMT, MOD, and RAND allow for quick computations. Statistical functions such as SUM and AVERAGE help analyze data effectively. Learn how to utilize these functions to manage financials, calculate dates, and perform statistical analyses in your spreadsheets.
Functions
E N D
Presentation Transcript
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 • Built-in functions: Formulas/Insert function • Financial • Date & time • Math & statistical • Database • Lookup • Logical • Text • Etc.
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
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
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.
Statistical Functions • Sum, Average, Max, Min, Count • Ignore text and logical values. • Can have many arguments: • =SUM(A1:A3, 12, B3:B7)
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
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
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
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)
Examples • Tomorrow’s date? • How many days to Christmas? • Compute the age given a date of birth.
Financial Functions • PV, NPV, FV • IRR • PMT, IPMT, PPMT,CUMIPMT
