MS Excel 2019 - Module 5: Using formulas and functions
Arithmetic Operators
Operator Operator Operator Example
Sign Name Meaning
+ Plus Addition Add Range A1 and B1
=A1+B1
- Minus Subtraction Subtract 100 from range B5
=B5-100
* Asterisk Multiplication Multiply two ranges
=A1*B1
/ Forward slash Division Divide Range B1 by A1
=B1/A1
^ Caret Exponentiation Square value of 25 can be
found using exponentiation
operator
=25^2
% Percent Percent Find the 25% of 1000
=25%*1000
Comparison Operators
Operator Operator Operator Example
Sign Name Meaning
= Equal to Equal to Checks if two expressions are equal
Comparison =IF(A1=A2,”Equal”,”Not Equal”)
> Greater than Greater than Checks if Expression 1 (Left) is Greater
Comparison than Expression 2 (Right)
=IF(A1>A2,”Bigger”,”Not Bigger”)
< Less than Less than Checks if Expression 1 (Left) is Less than
Comparison Expression 2 (Right)
=IF(A1<A2,”Smaller”,”Not Smaller”)
>= Greater than Greater than or Checks if Expression 1 (Left) is Greater
or equal to equal to than or Equal to Expression 2 (Right)
Comparison =IF(A1>=A2,”Greater than or
equal”,”Smaller”)
<= Less than or Less than or Checks if Expression 1 (Left) is Less than
equal to equal to or Equal to Expression 2 (Right)
Comparison =IF(A1<=A2,”Less than or
equal”,”Greater”)
<> Not equal to Not equal to Checks if two expressions are Not equal
Comparison =IF(A1<>A2,”Not equal”,”Equal”)
MS Excel 2019 - Module 5: Using formulas and functions
Basic Excel functions
Function Description Example
SUM Gives the total of the selected range of cell values. =SUM(C2:C4)
AVERAGE Gives the average of the selected range of cell values. =AVERAGE(C2,C3,C4)
COUNT Counts the total number of cells in a range that contains a =COUNT(B:B)
number. It does not include the cell, which is blank, and the
ones that hold data in any other format apart from
numeric.
COUNTA Counts all cells that are not blank, whether they contain =COUNTA(B:B)
numbers, dates, times, text, logical values of TRUE and
FALSE, errors or empty text strings ("").
MAX, Get the largest and smallest value in a set of numbers, =MAX(B2:B6)
MIN respectively. =MIN(B2:B6)
CONCAT Merges multiple strings or cells with strings into one. For =CONCAT(C2,B2)
example, if we want to join the age and sex of the athletes.
LEFT, The LEFT() function gives the number of characters from =LEFT(A2,9)
RIGHT, the start of a text string. Meanwhile, the MID() function =MID(A2,11,6)
MID returns the characters from the middle of a text string, =RIGHT(A2,7)
given a starting position and length. Finally, the right()
function returns the number of characters from the end of
a text string.
LEN Returns the total number of characters in a string. So, it will =LEN(A2)
count the overall characters, including spaces and special
characters.
UPPER, The UPPER() function converts any text string to uppercase. =UPPER(A6)
LOWER, In contrast, the LOWER() function converts any text string
PROPER to lowercase. The PROPER() function converts any text
string to proper case, i.e., the first letter in each word will
be in uppercase, and all the other will be in lowercase.
IF IF is similar to an if-else statement in a programming =IF(G2<25,"Fit”,"Unfit")
language. We will provide the logic of the formula. If the
logic is correct, it will return a certain value; if the logic is
False, it will return a different value.
