CAT Practical part of theory
Excel functions
Table 1: Common operators.
Operator Description Example
+ This operator is used to add values together. =C4+C5
- This operator is used to subtract values from =C4-C5
each other.
/ This operator is used to divide a value by =C4/C5
another value.
* This operator is used to multiply values. =C4*C5
> This operator is used to indicate values =C4>C5
greater than a specified value.
< This operator is used to indicate values =C4<C5
smaller than a specified value.
= This operator is used to indicate a value that =C4=C5
is equal to another.
<= This operator is used to indicate a value that =C4<=C5
is less than or equal to another value.
>= This operator is used to indicate a value that =C4>=C5
is greater than or equal to another value.
<> This operator is used to indicate a value that =C4<>C5
is less than or greater than but not equal to
another value.
"" This operator is used to indicate that the ="Anna"
value should be treated as text.
, This operator is used to separate arguments.
=SUM(C4,D4:D11,E11)
Table 2: Common functions.
Functions Description Example
SUM This function adds all the values in =SUM(C2:C20)
the specified cell range.
AVERAGE This function calculates the average =AVERAGE(C2:C20)
of the specified cell range.
COUNT This function counts the number of =COUNT(C2:C20)
cells that have a numerical value in
the specified cell range
COUNTIF This function will count the number of =COUNTIF(C2:C20,"Anna")
cells with specific criteria. For example,
this function will count the number of
times a certain name was mentioned.
COUNTA This function will return the number =COUNTA(C2:C20)
of non-blank cells in the cell range.
COUNT- This function will return the number =COUNTBLANK(C2:C20)
BLANK of blank cells in the cell range.
MIN This function finds the smallest =MIN(C2:C20)
numerical value in the specified cell
range.
MAX This function finds the greatest =MAX(C2:C20)
numerical value in the specified call
range.
SMALL This function will return the smallest =SMALL(C2:C20,2)
numerical value from a cell range.
LARGE This function will return the greatest =LARGE(C2:C20,4)
numerical value from a cell range.
TODAY This function inserts the current date =TODAY()
set by the computer.
NOW This function inserts the current date =NOW()
and time set by the computer.
MODE This function will show the most =MODE(C2:C20)
occurring value in a specified cell
range.
MEAN This function will show the middle =MEAN(C2:C20)
value in the specified cell range.
ROUND This function will round a number to a =ROUND(C4,2)
specified number of digits.
IF This function looks at the data in a =IF(A1>60,"Yes","No")
cell and checks if it meets a basic
condition.
SUMIF This function looks at the data in the
specified cell range and checks if it =SUMIF(C2:C20,criteria,D2:
meets a basic condition. If the D20)
condition is met, the sum of the values
in a second specified cell range is
returned.
POWER This function is used to calculate a =POWER(C2,D2)
given number raised to a supplied
power.
Table 3: Common error indicators.
Error Descriptions Solution
#### The column is not wide You can widen the columns.
enough to display the cell's
numerical value.
#NAME An incorrect function name is You can make sure that the
used. spelling of the function name is
correct.
#DIV/0 The formula is trying to divide You can correct the cell
by 0 or in a cell with no value. reference.
#REF The cell reference is not valid. You can make sure you used
the correct cell reference.
#VALUE! The cells have incorrect You can make sure the formula
values. is correct and that you are
referencing the correct cells.
#NUM! The function has numeric You can make sure you used
values that are not valid. the correct cell values.
Circular This occurs when the value You can correct the cell range.
reference you are calculating includes
the cell you are calculating in.
#NULL Incorrectly separation of two You can correctly separate the
or more cell references. cell references by using a
comma or colon.
Another error indicator is #N/A, which indicates that the formulae cannot
locate what it was asked to locate, such as using the LOOKUP function to
locate a value that does not exist. There are other possible causes of an
#N/A error, such as:
★ Looking for data that is not an exact match.
★ There is a spelling mistake in the value.
★ Specifying the wrong cell range.
Table 1: Example of the ROUNDUP and ROUNDDOWN functions.
Function Example
=ROUNDUP(cell, decimal value) =ROUNDUP(B2,2)
=ROUNDDOWN(cell, decimal value) =ROUNDDOWN(B2,2)
Table 2: Example of the SUMIF function.
Function Example
=SUMIF(cell range 1, condition, cell range 2) =SUMIF(B1:B30,">10",D1:D30)
Table 3: Example of the COUNTIF function.
Function Example
=COUNTIF(cell range, condition) =COUNTIF(B3:B15,"15")
Table 4: Example of the COUNTIFS and SUMIFS functions.
Function Example
=COUNTIF(cell range, condition) =COUNTIFS(B3:B15,"15",
D3:D15,"yes")
=COUNTIFS(cell range 1, condition 1, [cell range
2, condition 2]...)
=SUMIF(cell range 1, condition, additional cell =SUMIFS(D1:D30,B1:B30,>10,
range) H1:H30,>=30)
=SUMIFS(additional cell range, cell range 1,
condition 1, [cell range 2, condition 2]...)
Table 5: Example of a nested IF function.
Function Example
=IF(cell argument, "condition true", IF(cell =IF(C3>=80, "Distinction",
argument, "condition true", "condition false")) IF(C3>=40, "Pass", "Fail"))
