CompSc3
Computer
Fundamentals
NELSON G. LIWANEN JR.
Spreadsheet Functions
Learning Outcomes
At the end of the lesson, students
should be able to:
UseMS Excel functions in solving
mathematical problems.
Basic Excel Functions
SUM
AVERAGE
COUNT
MAX
MIN
IF
RANK
COUNTIF
SUMIF
VLOOKUP
SUM
The SUM function is the first must-know
formula in Excel. It usually aggregates values
from a selection of columns or rows from
your selected range.
Syntax: =SUM(number1, [number2], …)
SUM Example
=SUM(B2:G2) – A simple selection that sums the
values of a row.
=SUM(A2:A8) – A simple selection that sums the
values of a column.
=SUM(A2:A7, A9, A12:A15) – A sophisticated
collection that sums values from range A2 to A7,
skips A8, adds A9, jumps A10 and A11, then finally
adds from A12 to A15.
=SUM(A2:A8)/20 – Shows you can also turn your
function into a formula.
AVERAGE
The AVERAGE function should remind you of simple
averages of data such as the average number of
shareholders in a given shareholding pool.
Syntax: =AVERAGE(number1, [number2], …)
Example:
=AVERAGE(B2:B11) – Shows a simple average, also
similar to (SUM(B2:B11)/10)
COUNT
The COUNT function counts all cells in a given range
that contain only numeric values.
Syntax: =COUNT(value1, [value2], …)
Example:
COUNT(A:A) – Counts all values that are numerical in A
column. However, you must adjust the range inside the
formula to count rows.
COUNT(A1:C1) – Now it can count rows.
MAX and MIN
The MAX and MIN functions help in finding the
maximum number and the minimum number in a
range of values.
Syntax: =MIN(number1, [number2], …)
Example:
• =MIN(B2:C11) – Finds the minimum number between
column B from B2 and column C from C2 to row 11 in both
columns B and C.
MAX and MIN
Syntax: =MAX(number1, [number2], …)
Example:
➢ =MAX(B2:C11) – Similarly, it finds the maximum
number between column B from B2 and column
C from C2 to row 11 in both columns B and C.
IF
The Microsoft Excel IF function returns one
value if the condition is TRUE, or another
value if the condition is FALSE.
Syntax
=IF(condition, [value_if_true], [value_if_false])
IF
Parameters or Arguments
condition: The value that you want to test.
value_if_true: It is the value that is returned
if condition evaluates to TRUE.
value_if_false:Optional. It is the value that is
returned if condition evaluates to FALSE.
IF
Returns
TheIF function returns value_if_true when the
condition is TRUE.
TheIF function returns value_if_false when the
condition is FALSE.
The IF function returns FALSE if
the value_if_false parameter is omitted and the
condition is FALSE.
IF Example
=IF(B2<10, "Reorder", "") Result: "Reorder"
=IF(A2="Apples", "Equal", "Not Equal") Result:
"Equal"
=IF(B3>=20, 12, 0) Result: 12
RANK
The Microsoft Excel RANK function returns the rank
of a number within a set of numbers.
Syntax
RANK( number, array, [order] )
Parameters or Arguments
number: The number to find the rank for.
array: A range or array of numbers to use for ranking
purposes.
order: Optional. It specifies how to rank the numbers.
RANK
Returns
The RANK function returns a numeric value.
Note
If
order is 0, the numbers are ranked in
descending order.
If
order is not 0, the numbers are ranked in
ascending order.
If
the order parameter is omitted, it assumes
order is 0 (descending order).
RANK Example
=RANK(A4, A2:A6, 1) Result: 5
=RANK(A4, A2:A6, 0) Result: 1
=RANK(A3, A2:A6, 0) Result: 4
COUNTIF
The Microsoft Excel COUNTIF function counts
the number of cells in a range, that meets a
given criteria.
Syntax
COUNTIF( range, criteria )
Parameters or Arguments
range: Therange of cells that you want to count
based on the criteria.
criteria: The criteria used to determine which cells
to count.
COUNTIF
Returns
The COUNTIF function returns a numeric value.
Example
=COUNTIF(A2:A7, D2) Result: 1
=COUNTIF(A:A, D2) Result: 1
=COUNTIF(A2:A7, ">=2001") Result: 4
SUMIF
The SUMIF function is a worksheet function
that adds all numbers in a range of cells
based on one criteria.
Syntax:
SUMIF( range, criteria, [sum_range] )
SUMIF
Parameters or Arguments
range: The range of cells that you want to apply
the criteria against.
criteria: The criteria used to determine which cells
to add.
sum_range: Optional. It is the range of cells to
sum together. If this parameter is omitted, it
uses range as the sum_range.
Returns
The SUMIF function returns a numeric value.
SUMIF Example
=SUMIF(A2:A6, D2, C2:C6) Result: 218.6 'Criteria is the
value in cell D2
=SUMIF(A:A, D2, C:C) Result: 218.6 'Criteria applies
to all of column A (ie: A:A)
=SUMIF(A2:A6, 2003, C2:C6) Result: 7.2 'Criteria is the
number 2003
=SUMIF(A2:A6, ">=2001", C2:C6) Result: 12.6 'Criteria
is greater than or equal to 2001
VLOOKUP
The VLOOKUP function performs a vertical
lookup by searching for a value in the first
column of a table and returning the value in
the same row in the index_number position.
Syntax
VLOOKUP( value,table, index_number,
[approximate_match] )
VLOOKUP
Parameters or Arguments
value: The value to search for in the first column
of the table.
table:Two or more columns of data that is sorted
in ascending order.
index_number: The column number in table from
which the matching value must be returned. The
first column is 1.
approximate_match: Optional. Enter FALSE to
find an exact match. Enter TRUE to find an
approximate match. If this parameter is omitted,
TRUE is the default.
VLOOKUP
Returns
The VLOOKUP function returns any datatype such as a
string, numeric, date, etc.
If you specify FALSE for the approximate_match parameter
and no exact match is found, then the VLOOKUP function
will return #N/A.
If you specify TRUE for the approximate_match parameter
and no exact match is found, then the next smaller value is
returned.
If index_number is less than 1, the VLOOKUP function will
return #VALUE!.
If index_number is greater than the number of columns
in table, the VLOOKUP function will return #REF!.
VLOOKUP Example
=VLOOKUP(10251, A1:B6, 2, FALSE) Result: "Pears"
'Returns value in 2nd column
=VLOOKUP(10251, A1:C6, 3, FALSE) Result: $18.60
'Returns value in 3rd column
=VLOOKUP(10251, A1:D6, 4, FALSE) Result: 9 'Returns
value in 4th column
References/Sources
https://corporatefinanceinstitute.com/resources/
excel/study/basic-excel-formulas-beginners/
https://www.techonthenet.com/excel/formulas/in
dex.php
