F.4Information and Communication Technology (09-10) WSLee p.1

Po Leung Kuk Ngan Po Ling College

Information and Communication Technology

Spreadsheets

Revision:

  1. What is the function of AutoFill?

It allows users to create a series of values based on the value of one or more selected cells adjacent to each other. Copy and Paste and Extending the Series are two ways of Autofill function
  1. Complete the following table:

Element / Description
Operator / Arithmetic operators
+ addition / - subtraction / * multiplication / / division
^ exponentiation / % percent
Comparison operators
= equal to / > not equal to / > greater than / >= greater than or equal to
< less than / <= less than or equal to
Statistical Function
MAX( ) / Return the maximum value within a range of cells
MIN( ) / Return the minimum value within a range of cells
AVERAGE( ) / Return the average of the values within a range of cells
COUNT( ) / Return the number of cells containing numerical data within a range of cells
COUNTIF( ) / Return the number of cells satisfying the specified condition within a range of cells
LARGE( ) / Return the kth largest value within a range of cells
SMALL( ) / Return the kth smallest value within a range of cells
MODE( ) / Return the value with the highest frequency of occurrence within a range of cells
SUM( ) / Sum up all the numbers within a range of cell
SUMIF( ) / Sum up the cell values within the <sum range>if the corresponding entries in the <cell range> match the criteria
ROUND( ) / Round x to the number with D decimal places
INT( ) / Round x down to the nearest integer
MOD( ) / Return the remainder of x / D
POWER( ) / Return the value of X ^ D
RAND() / Return a random number greater than or equal to 0 and less than 1

Logical Functions:

Fill in the blanks:

e.g. if there is an elephant in the library, then I’ll give you 100 dollars.

  1. Suppose there really is an elephant in the library, and I give you 100 dollars. Then if the whole sentence true or not?

True
  1. Suppose there really is an elephant in the library, but I don’t give you any money. Then if the whole sentence true or not?

False
  1. Suppose there is no elephant in the library and I don’t give you any money. If the whole sentences true or not?

True
  1. Suppose there is no elephant in the library and I give you 100 dollars anyway. If the whole sentences true or not?

True

e.g. if there is an elephant in the library, then I’ll give you 100 dollars.

Which is condition? Which is output?

There is an elephant in the library

In excel, the application of IF function is shown as below:

If(<condition>, X, Y)

  1. Rewrite the following formula to sentence.

=IF(B4>=50, “Pass”, “Fail”)

If the value of B4 is greater than 50, then the output is pass, otherwise, the output is Fail
  1. Write down the output if the value in B4 is “30”

Fail

In excel, the application of “AND” function is shown as below:

AND(<condition1>, <condition2>, …)

  1. Discuss with your classmate, what is the meaning of “AND”?
  1. Suppose there really is an elephant in the library AND there is a lion in the library, and I give you 100 dollars. Then if the whole sentence true or not?

True
  1. Suppose there really is an elephant in the library AND there is no lion in the library, but I give you any money. Then if the whole sentence true or not?

False
  1. Suppose there is no elephant in the library AND there is a lion in the library, but I give you any money. If the whole sentences true or not?

False
  1. Suppose there is no elephant in the library AND there is no lion in the library, and I give you 100 dollars anyway. If the whole sentences true or not?

False

If P = condition 1, Q = condition 2

If T = true, F = false

P / Q / P and Q
T / T / T
T / F / F
F / T / F
F / F / F

In excel, the application of “OR” function is shown as below:

OR(<condition1>, <condition2>, …)

  1. Discuss with your classmate, what is the meaning of “OR”?
  1. Suppose there really is an elephant in the library OR there is a lion in the library, and I give you 100 dollars. Then if the whole sentence true or not?

True
  1. Suppose there really is an elephant in the library OR there is no lion in the library, but I give you any money. Then if the whole sentence true or not?

True
  1. Suppose there is no elephant in the library OR there is a lion in the library, but I give you any money. If the whole sentences true or not?

True
  1. Suppose there is no elephant in the library OR there is no lion in the library, and I give you 100 dollars anyway. If the whole sentences true or not?

False

If P = condition 1, Q = condition 2

If T = true, F = false

P / Q / P OR Q
T / T / T
T / F / T
F / T / T
F / F / F