Lecture No.
3
Using Formulas & Equations
Excel includes arithmetic operators,
string operators, and comparison
operators.
The arithmetic operators combine
numerical values (called operands) to
produce a numerical value.
The string operator is used to combine
(i.e., to concatenate) two strings.
The comparison operators compare one
operand with another, resulting in a
condition that is either true or false.
Addition +
Subtraction -
Multiplication *
Division /
Exponentiation ^
Percentage (divide by 100) %
Greater than >
Greater than or equal to >=
Less than <
Less than or equal to < =
Equal (equivalent) =
Not equal (not equivalent)< >
1) Percentage (%)
2) Exponentiation (^)
3) Multiplication and Division (*
and /)
4) Addition and Subtraction (+ and -)
5) Concatenation (&)
6) Comparisons (>, >=, <, <=, =,
<>)
We have already seen that formulas express
interdependencies among the values in
different cells within a worksheet.
Formulas allow you to perform arithmetic
operations on numerical values, combine
strings, and compare the contents of one cell
with another.
In Excel, formulas begin with an equal sign (=),
followed by a numerical expression involving
constants, operators, and cell addresses.
Consider, for example, the formula
=(C3+B2+5).
A function consists of a function name,
followed by one or more arguments
Arguments are enclosed in
parentheses and separated by commas
To create a new calculation, you click
Insert Function on the Formulas menu.
The Insert Function dialog box appears,
with a list of functions, or predefined
formulas, from which you can choose.
Item Description
SUM Returns the sum of the numbers in the specified
cells
AVERA Finds the average of the numbers in the specified
GE cells
COUNT Finds the number of entries in the specified cells
MAX Finds the largest value in the specified cells
MIN Finds the smallest value in the specified cells
SUM(C1,C2,C3). This function calculates the
sum of the quantities in cells C1, C2, and C3.
Consider, for example, the function
specification SUM(A1, SQRT(A2/2), 2*B3+5,
D7:D12).
The arguments in an If statement are =IF(logical test,
“Value if true","Value if false"), whereby the meanings
are as follows:
Logical test – what are we asking? In this case, let’s
say our logical test is “Is the value in Column A larger
than the value in Column B?”
Value if true – what do we want displayed if the
answer to our question is “true”? In this example, let’s
say we want the answer to be “yes”
Value if false – what do we want displayed if the
answer is “false”? In this example, let’s say we want
In a formula, the arguments are
separated by commas, so for this
example, let’s put our formula in cell C2
and this is what it would look like:
=IF(A2>B2,"yes","no")
This says, IF the value in A2 is greater
than the value in B2, put “yes” in C2 and
if it’s not greater than B2, put “no” in C2.
Description
The IF function returns one value if a
condition you specify evaluates to TRUE,
and another value if that condition
evaluates to FALSE. For example, the
formula =IF(A1>10,"Over 10","10 or
less") returns "Over 10" if A1 is greater
than 10, and "10 or less" if A1 is less than
or equal to 10.
Syntax –
IF(logical_test, value_if_true, [value_if_false])
Up to a certain number of conditions IF
functions can be nested as
value_if_true and value_if_false
arguments to construct more elaborate
tests. (See Example 3 for a sample of
nested IF functions.)
Alternatively, to test many conditions,
consider using the LOOKUP, VLOOKUP,
Excel provides additional functions that can
be used to analyze your data based on a
condition.
For example, to count the number of
occurrences of a string of text or a number
within a range of cells, use the COUNTIF or
the COUNTIFS worksheet functions.
To calculate a sum based on a string of text
or a number within a range, use the SUMIF or
the SUMIFS worksheet functions HLOOKUP, or
CHOOSE functions.
=IF(A2<=100,"Within budget","Over
budget")
If the number in cell A2 is less than or
equal to 100, the formula returns
"Within budget." Otherwise, the function
displays "Over budget."
=IF(A2=100,A2+B2,"")
If the number in cell A2 is equal to 100,
A2 + B2 is calculated and returned.
Otherwise, empty text ("") is returned
=IF(A2>B2,"Over Budget", "OK")
Checks whether the expenses in row 2
are over budget
=IF(A3>B3, "Over Budget", "OK")
Checks whether the expenses in row 3
are over budget
A B C
Score Formula Description Result
45 =IF(A2>89,"A",IF(A2>79,"B", Assigns a F
IF(A2>69,"C",IF(A2>59,"D"," letter grade to
F")))) the score in
cell A2
90 =IF(A3>89,"A",IF(A3>79,"B", Assigns a A
IF(A3>69,"C",IF(A3>59,"D"," letter grade to
F")))) the score in
cell A3
78 =IF(A4>89,"A",IF(A4>79,"B", Assigns a C
IF(A4>69,"C",IF(A4>59,"D"," letter grade to
F")))) the score in
cell A4
In this example, the LOOKUP function
is used instead of the IF function
because there are many conditions to
test.
You may find the LOOKUP function
easier to read and maintain than the
IF function.
A B C
Score Formula Description Result
45 =LOOKUP(A2, Assigns a F
{0,60,63,67,70,73,77,80,83,87,90,9 letter grade
3,97}, to the score
in cell A2
{"F","D-","D","D+","C-","C","C+","B-
","B","B+","A-","A","A+"})
90 =LOOKUP(A3, Assigns a A-
{0,60,63,67,70,73,77,80,83,87,90,9 letter grade
3,97}, to the score
in cell A3
{"F","D-","D","D+","C-","C","C+","B-
","B","B+","A-","A","A+"})
78 =LOOKUP(A4, Assigns a C+
{0,60,63,67,70,73,77,80,83,87,90,9 letter grade
3,97}, to the score
in cell A4