1 / 19

Functions

Functions. Types of Functions. Built-in functions: Financial Date & time Math & statistical Database Lookup Logical Information: IsBlank, IsNumber, IsText Text Etc. Arguments. Arguments are inputs to a function. A function may or may not require arguments. Example:

burton
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

  2. Types of Functions • Built-in functions: • Financial • Date & time • Math & statistical • Database • Lookup • Logical • Information: IsBlank, IsNumber, IsText • Text • Etc.

  3. Arguments • Arguments are inputs to a function. • A function may or may not require arguments. • Example: • No argument: Today() • One argument: • IsBlank(A1) • Upper(“David”) • More than one: PMT

  4. A few math functions • Int(x): Rounds a number down to the nearest integer • Int(2.3) = 2 • Int(5.6) = 5 • Int(-2.7) = -3 • QUOTIENT(numerator,denominator) • Returns the integer portion of a division • Numerator     is the dividend. • Denominator     is the divisor. • MOD(Dividend, Divisor) • Returns the remainder of a division

  5. Examples of Using Int Function Remainder function: MOD(Dividend, Divisor)

  6. Rounding • ROUND(number, num_digits) • It rounds a number to a specified number of digits. • ROUNDUP(number,num_digits) • ROUNDDOWN(number,num_digits)

  7. Currency Format & Rounding • Example: • Enter 3.543 in cell A1and enter 3.123 in cell A2 • Enter Sum(A1:A2) in cell A3 • Assign currency format with 2 decimals to A1:A3 • Stored value and displayed value

  8. Statistical Functions • Sum, Average, Max, Min, Count • Ignore text and logical values. • SumA, AverageA, MaxA, MinA, CountA • Does not ignore text and logical values: • Text -> 0 • True -> 1 • False -> 0 • Can have many arguments: • =SUM(A1:A3, 12, B3:B7)

  9. Using Statistical Functions • Compute each student’s: • Best score, Lowest score, exam average • Average of the best 2 scores • 3. Weighted avg: 50%*Best score + 30% * 2nd best + 20%*lowest

  10. 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. Text Operator • Concatenation: & • Join two or more text values into a single text value.

  14. Concatenate(text1, text2,…) Lower(text): Lower(“David”) -> “david” Upper(text): Upper(“David”) -> “DAVID” Right(text, #ofCHars) Right(“David”, 4) -> ”avid” Left(text, #ofChars) Left(“David”, 2) -> “Da” Len(text) -> the number of characters in a text. Len(“David”) -> 5 Text Functions

  15. The first letter in uppercase and all other letters in lowercase

  16. The first letter in each word in uppercase and other in lower case Proper(text)

  17. Financial Functions • Payment: PMT • Interest payment: IPMT • Principal payment: PPMT • Cumulative interest:CUMIPMT • Cumulative principal payment: CUMPRIC

  18. A few interesting formatting skills • Home • Font group: Border, Fill Color, Font Color • Style group: Cell Style, Conditional Formatting • Alignment: Merge • Page layout • Themes • Page Setup group: Background • Sheet Options group: View Gridline

More Related