Excel Function Sheet
Structure of the function
function name (argument 1, argument 2)
- arguments are separated by the use of the comma (,)
Function What it does?
Basic Arithmetic Functions
SUM(cell_range) finds the total of the cell range provided
AVERAGE(cell_range) finds the average of the cell range provided
COUNT(cell_range) finds the number of rows of the cell range provided
cannot count the text data
COUNTA(cell_range) finds the number of rows of the cell range provided including the text data
MAX(cell_range) finds the maximum value from the cell range provided
MIN(cell_range) finds the minimum value from the cell range provided
Round Functions
ROUND(value, number_digit) rounds the value to the nearest value
checks the number behind, if the number is greater than or eqal to 1, add 1
if it is not greater than or equal to 1, do not add 1
value – value to round
number digit – the digit to round to (0 = 0 decimal place, 1 = 1 decimal
place, 2 = two decimal places, -1 = tenth place, -2 = hundredth place etc.)
ROUND(value, number_digit) rounds the value up
add 1 if there is any value behind the number e.g. 24.00001 will be 25
value – value to round
number digit – the digit to round to (0 = 0 decimal place, 1 = 1 decimal
place, 2 = two decimal places, -1 = tenth place, -2 = hundredth place etc.)
ROUND(value, number_digit) rounds the value down
do not add 1 no matter what value there behind the number e.g. 24.99 will
still be 24
value – value to round
number digit – the digit to round to (0 = 0 decimal place, 1 = 1 decimal
place, 2 = two decimal places, -1 = tenth place, -2 = hundredth place etc.)
Logical Functions
IF(logical_test, if_true, checks the condition do a thing if the condition is true and do another thing
if_false) if the condition is false
logical test – a yes/no question e.g B5 >= 50
if_true – what the Excel should do if the condition is true
if_false – what the Excel should do if the condition is false
AND(logical_test_1, checks multiple logical test
logical_test_2, … ) returns true only if all the logical tests are true
OR(logical_test_1, checks multiple logical test
logical_test_2, … ) returns true only if any logical test is true
Arithmetic Functions with Conditions
COUNTIF(range, criteria) counts the number of rows that matches the criteria
works for both the number and text values
range – cell range to count and check the condition upon
criteria – condition for the range
SUMIF(range, criteria, finds the total of the cell range provided, that matches the criteria
sum_range) range – cell range to check the condition upon
criteria – condition for the range
sum_range – cell range to find the total from
AVERAGEIF(range, criteria, finds the average of the cell range provided, that matches the criteria
sum_range) range – cell range to check the condition upon
criteria – condition for the range
sum_range – cell range to find the average from
Lookup Function
VLOOKUP(lookup_value, finds the value that is corresponding with the lookup_value from the
table_array, column_index, table_array and returns the value
range_lookup) lookup_value – the value that will be used to search the result
table_array – the cell range that includes both the lookup value and the
result
column_index – the column that the result is in the table array
range_lookup – TRUE for approximate match and FALSE for exact match
