Transcript Chapter 1 Introduction - Wah Yan College, Kowloon

Chapter 5 Basic Functions
Copyright 2005 Radian Publishing Co.
Contents
Chapter 5 Basic Functions
5.1 Introduction to Functions
• 5.1 A. Concept about Functions
• 5.1 B. Entering a Function
5.2 Mathematical Functions
5.3 Statistical Functions
5.4 Date and Time Functions
5.5 Text Functions
2/22
Copyright 2005 Radian Publishing Co.
Chapter 5 Basic Functions
The Excel built-in functions are classified into eight categories:
•
•
•
•
•
•
•
•
3/22
Financial,
Date and Time
Mathematical and trigonometry
Statistical
Lookup and Reference
Database
Text
Logical and Information
Copyright 2005 Radian Publishing Co.
5.1 Introduction to Functions
To make use of the computing power of your spreadsheet software,
you should be familiar with certain basic functions.
4/22
Copyright 2005 Radian Publishing Co.
5.1 A. Concept about Functions
(1/2)
A function is placed inside a formula and consists of a function name
and one or more arguments.
An argument is an input data which may be a value, a cell reference,
reference to a range of cells, or another function.
A function carries out operations based on the argument(s) and, in
general, returns a single result.
In general, a function returns a value, but some functions may return
multiple values.
An array function returns multiple values.
5/22
Copyright 2005 Radian Publishing Co.
5.1 A. Concept about Functions
Fig.5.1 A function accepts input and returns a single result
6/22
Copyright 2005 Radian Publishing Co.
(2/2)
5.1 B. Entering a Function
You may insert a function by using keyboard or using Insert Function
dialog box.
Fig.5.3 Insert Function dialog box
7/22
Copyright 2005 Radian Publishing Co.
5.2 Mathematical Functions
(1/3)
The mathematical functions that you have learnt are:
1. TRUNC(numbernumber) removes the integral part of a real number.
2. INT(numbernumber) rounds a number down to the nearest integer
taking into account the sign.
3. ROUNDDOWN(m, n) rounds down the magnitude of the number m
to n digits.
4. ROUND(m, n) rounds a number m to n digits.
5. ROUNDUP(m, n) rounds up the magnitude of the number m to n
digits.
8/22
Copyright 2005 Radian Publishing Co.
5.2 Mathematical Functions
(2/3)
6. RAND() returns a random number between 0 and 1 (not including 1).
The formula for generating random real numbers between a and b
(not including b) is =RAND()*(b - a ) + a.
The formula to produce a random integer in the range from p to q
inclusively is =INT(RAND()*(q - p + 1)) + p.
The formula to generate a random date between two date, D1 and
D2 (D1<D2), is =INT(RAND()*(D2-D1+1))+D1.
9/22
Copyright 2005 Radian Publishing Co.
5.2 Mathematical Functions
(3/3)
7. MOD(x, y) returns the remainder after x is divided by y and has the
same sign as the divisor.
=MOD(x,yx,y) is equivalent to =x - INT(x/yx/y)*y.
8. QUOTIENT(x, yx, y) returns the integral part after x is divided by y
and is equivalent to =TRUNC(x/yx/y).
9. ABS(number) returns the absolute value of a number. The absolute
value of a number is the number without its sign.
10. SQRT(number) returns the square root of a number.
10/22
Copyright 2005 Radian Publishing Co.
5.3 Statistical Functions
(1/5)
The statistical functions that you have learnt are:
1. SUM(range) adds all the values in the arguments.
The arguments may be numbers or be names, or references that
contain numbers.
Non-numerical arguments in cells are ignored. But, non-numerical
arguments used directly will be translated.
2. AVERAGE(range) returns the average (mean) of the arguments.
3. COUNT(range) counts the number of cells that contain numbers.
Non-numerical data will be ignored.
11/22
Copyright 2005 Radian Publishing Co.
5.3 Statistical Functions
(2/5)
4. COUNTA(range) counts the number of cells that are not empty.
5. COUNTBLANK(range) counts the number of empty cells in a
specified range.
6. MAX(range) returns the largest value in a set of values.
7. MIN(range) returns the smallest value in a set of values.
8. LARGE(range, k) returns the kth largest value in a data set, where k
is 1, 2, 3, …
9. SMALL(range, k) returns the kth smallest value in a data set, where k
is1, 2, 3, …
12/22
Copyright 2005 Radian Publishing Co.
5.3 Statistical Functions
(3/5)
10. RANK(number, reference, ordernumber, reference, order) returns
the rank of a number in a list of numbers.
If order is 0 or omitted, Excel ranks the highest score as 1 (i.e.
descending order). Otherwise, Excel ranks the lowest score as 1
(i.e. ascending order).
If two numbers in the list are equal, they will have the same rank.
The succeeding rank will be skipped.
13/22
Copyright 2005 Radian Publishing Co.
5.3 Statistical Functions
(4/5)
11. MEDIAN(range) returns the median of the given numbers.
If the number of data in the set is even, then MEDIAN calculates
theaverage of the two numbers in the middle.
12. MODE(range) returns the most frequently occurring, or repetitive,
value in an array or range of data.
14/22
Copyright 2005 Radian Publishing Co.
5.3 Statistical Functions
(5/5)
AVERAGE(range) can be implemented by SUM(range)/COUNT(range).
LARGE(range, 1) is the same as MAX(range, 1).
SMALL(range, 1) is the same as MIN(range, 1).
15/22
Copyright 2005 Radian Publishing Co.
5.4 Date and Time Functions
(1/4)
The date and time functions that you have learnt are:
1. NOW() returns the serial number of the current date and time.
2. TODAY() returns the serial number of the current date.
3. DATE(year, month, day) returns the serial number that represents a
particular date.
4. DAY(date), MONTH(date) and YEAR(date) return the day, month
and year of a date respectively.
YEAR(date) returns an integer ranging from 1900 to 9999.
16/22
Copyright 2005 Radian Publishing Co.
5.4 Date and Time Functions
(2/4)
5. WEEKDAY(date) returns the day of the week corresponding to a date.
The result 1 represents Sunday, 2 Monday and 7 Saturday, by default.
17/22
Copyright 2005 Radian Publishing Co.
5.4 Date and Time Functions
(3/4)
The formula to find the date after a given number of years (C3), months
(C4) and days (C5) of a given date (C2) is
=DATE(YEAR(C2)+C3,MONTH(C2)+C4,DAY(C2)+C5)
The formula to find the number of days in a given year (B2) is
=DATE(B2+1,1,1)-DATE(B2,1,1)
The formula to convert a weekday (of a date A3) into its English form is
=TEXT(WEEKDAY(A3),"ddd").
18/22
Copyright 2005 Radian Publishing Co.
5.4 Date and Time Functions
(4/4)
Suppose D1 stores the date of birth of a person and D2 stores the
current date. The formula to calculate the age of the person is
=IF(DATE(1900,MONTH(D1),DAY(D1)) <=
DATE(1900,MONTH(D2),DAY(D2)),
YEAR(D2)-YEAR(D1),
YEAR(D2)-YEAR(D1)-1)
or,
=IF(OR(MONTH(D1)<MONTH(D2),AND(MONTH(D1)=MONTH(D2),DAY(
D1)<= DAY(D2))),
YEAR(D2)-YEAR(D1),
YEAR(D2)-YEAR(D1)-1)
19/22
Copyright 2005 Radian Publishing Co.
5.5 Text Functions
(1/3)
The text functions that you have learnt are:
1. LEN(text) returns the number of characters in a text string.
2. LOWER(text) converts text to lowercase.
3. UPPER(text) converts text to uppercase.
4. PROPER(text) capitalizes the first letter in each word of a text
value.
5. TRIM(text) removes all spaces from text except for single spaces
between words.
20/22
Copyright 2005 Radian Publishing Co.
5.5 Text Functions
(2/3)
6. CODE(text) returns the numeric ASCII code for the first character in
a text string.
7. CHAR(number) returns the character corresponding to the code
number.
8. LEFT(text, n) returns the first n character(s) in a text string.
9. RIGHT(text, n) returns the last n character(s) in a text string.
10. MID(text, m, n) returns n characters from a text string starting at
position m.
21/22
Copyright 2005 Radian Publishing Co.
5.5 Text Functions
(3/3)
11. TEXT(value, format_text) converts a value to text according to a
specified number format.
12. VALUE(text) converts a text string that represents a number to a
number.
13. REPT(text, n) repeats text a for n times.
LEFT(x, n) is equivalent to MID(x, 1, n).
RIGHT(x, n) is equivalent to MID(x, LEN(x) -n + 1, n).
22/22
Copyright 2005 Radian Publishing Co.