Textbook Notes (368,439)
Canada (161,878)
Dr.Hurst (18)
Chapter

Excel Session 7.docx

7 Pages
45 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Dr.Hurst
Semester
Winter

Description
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: o IF o AND o OR 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 time. o The lookup column does not have to be in ascending order for false scenarios.  The conditions use the following operators: o < o <= o = o <> o > o >= 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 column. 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 Syntax. 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 FALSE. 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 following Syntax: 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 increase. Interpreting the OR and IF function: o =IF(OR(F2=”Home”,G2=”FT”),K2*0.04,K2*0.025) ****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: Tablename[qualifier]  A structured reference can be fully qualified or unqualified. o For a fully qualified structured reference, the table name precedes the column qualifier  Ex. =IF(Employee[Add Life Ins]= “Y”,Employee[Annual Salary]*.0001,0) o For a unqualified structured reference, only the column qualifier appears in the reference.  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 formula.  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 the
More Less

Related notes for COMPSCI 1BA3

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit