Comp Sci Session 7: Using Advance
Functions and Conditional Formatting
Comp Sci Session 7.1: Logical Functions
Working with Logical Functions (pg. 116)
Logical functions such as:
These functions determine whether a condition is TRUE or FALSE.
True is an approximate match
o A lookup column must be in ascending order for true scenarios
False is an exact match.
o Usually a non numerical value such as text but not always, just most of the
o The lookup column does not have to be in ascending order for false scenarios.
The conditions use the following operators:
These operators are used to compare 2 values.
Inserting Calculated Columns in an Excel Table (pg.116)
When adding a new column to an Excel table, the format is generically the same as
the other columns.
If you enter one formula to a cell of that column, that whole column will
automatically have that same formula in each of the cells.
o Same goes for modifying a formula. If you input a formula and you later want
to alter it, the altered formula would apply to all the cells within that same
Comp Sci Session 7: Using Advanced Functions and Conditional Formatting Using the IF Function (pg. 117)
The IF function evaluates a single condition and generates one value if the condition
is true and another value if the condition is false. The IF function has the following
IF (logical_test, [value_if_true,] [value_if_false])
o logical_test is the condition that is either true or false.
o value_if_true is the value if the condition is true.
o value_if_false is the value if the condition is false.
The IF function can only have one value. The single value can either be from value_if_true
or from value_if_false.
****Refer to Figure 7-2, 7-3, 7-4 ****
Using the AND Function (pg. 120)
The AND function evaluates 2 or more conditions and generates a value TRUE if all
the logical conditions are true and the value FALSE if any or all of the logical
conditions are false. The AND function has the following Syntax:
AND (logical1 *, logical2,+…..)
o Logical1 and logical2 are conditions that can be either true or false.
o If all of the logical conditions are true, the AND function returns all the logical
value TRUE or vice versa for FALSE.
NOTE: All the logical conditions listed in the AND function must be true for the
AND function to return a TRUE value.
****Refer to Figure 7-6****
You will need to interpret the IF and AND function to determine whether a value is TRUE or
o Ex. =IF(AND(G2=”FT”,M2>=1),K2*0.03,0)
The last value is 0. It is used to represent if the function is false. AND
function returns it as false, IF function displays it as 0.
****Refer to Figure 7-7****
Using the OR function (pg. 124)
The OR function generates a TRUE value if any of the logical conditions are true and
a FALSE value if all of the logical conditions are false. The OR function has the
Comp Sci Session 7: Using Advanced Functions and Conditional Formatting 2 OR (logical1 *, logical2,+…..
o Logical1 and logical 2 are conditions that can be either true or false.
o If all of the logical conditions are true, the OR function returns all the logical
value TRUE or vice versa for FALSE.
The OR function is used when there are two scenario’s.
o Ex. Test if the employee meets the criteria to have either a 4% or 2.5% salary
Interpreting the OR and IF function:
****Refer to Figure 7-11 & 7-12****
Using Structured References to Create Formulas in Excel Tables (pg. 126)
When you create a formula that reference to parts of the Excel table, you can replace the
specific cell or range with a,
o Structured reference
o Actual table name
o Column header
Column headers provide a description of the data entered in each column.
Structured References makes it easier to create formulas that use portions or all of
an Excel table.
When you want to reference an entire column of data in a table, you create a column
qualifier, which has the Syntax:
A structured reference can be fully qualified or unqualified.
o For a fully qualified structured reference, the table name precedes the column
Ex. =IF(Employee[Add Life Ins]= “Y”,Employee[Annual Salary]*.0001,0)
o For a unqualified structured reference, only the column qualifier appears in
Ex. =IF([Add Life Ins}= “Y”,[Annual Salary]*.001,0)
When creating a calculated column or formula within a Excel table, you can use either
the fully qualified structured reference or the unqualified structured reference in the
You must use a fully qualified reference if:
o The structured reference is outside the table.
o In another worksheet to reference an Excel table.
o Portion of the table.
****Session 7.1 Review Questions pg. 129***
Comp Sci Session 7: Using Advanced Functions and Conditional Formatting 3 Comp Sci Session 7.2: Nested IF’s and Lookup Table
Creating Nested IF’s (pg. 132)
IF function can choose between two outcomes; it cannot choose three or more outcomes.
However you can nest IF functions to allow 3 or more outcomes.
A nest IF function is when one IF function is placed inside another IF function to test
an additional condition.
o Ex. =IF([Pay Grade]=12,500,IF([Pay Grade]=25,000, 7500))
Refer to Figure 7-16, 7-17, 7-18.
Problem Solving: Finding and Fixing Errors in Formulas:
Two common categories of formula errors in Excel are:
o Syntax error
o Logic error
Syntax error violates the rules in Excel. Syntax error might occur due to unmatched
parentheses or a required argument that is omitted in a function.
Logic error occurs in formulas. A logic error could occur because the formula uses