1 / 16

Functions

Functions. Types of Functions. Built-in functions: Financial Date & time Math & statistical Database Lookup Logical Information: IsBlank, IsNumber, IsText Text Etc. User-defined functions. Int function. Int(x): return the greatest integer less than or equal to X. Int(2.3) = 2

josh
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. • User-defined functions

  3. Int function • Int(x): return the greatest integer less than or equal to X. • Int(2.3) = 2 • Int(5.6) = 5 • Int(-2.7) = -3, Trunc(-2.7) = -2

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

  5. 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

  6. Statistical Functions • Sum, Average, Max, Min, Count • Ignore text and logical values. • 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)

  7. Compute each student’s: Best score, Lowest score, exam average Average of the best 3 scores Average of the best 2 scores

  8. 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

  9. Average Excluding the Highest and the lowest Scores

  10. 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

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

  12. The first letter in each word in uppercase and other in lower case Full name format: First Name + Space + Last Name

  13. Proper • B1: DAVID CHAO • =Proper(B1)

  14. PMT Function • Calculates the payment for a loan based on constant payments and a constant interest rate. • Syntax • PMT(rate,nper,pv) • Rate    is the interest rate for the loan. For monthly payment, annual interest rate must be divided by 12 to get the monthly rate. • Nper    is the total number of payments for the loan. • Pv    is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

  15. Practices

More Related