Unit 4: Logical and Lookup Functions In this unit we will be looking at Chapter 3 of the textbook, which uses more advanced functions. As the chapter explains these functions, go beyond the basic functions of Excel®. The IF function can be used in many different instances that use numbers or text as the answer. Remember that a comma separates the different parts of each function so you should not type commas in large numbers. You should format the numbers after the calculations have been made. We humans need to separate numbers for easier reading, not the software program. You will be nesting one function inside another. This will take some logical thinking. Although you may not need to be a mathematician to master Excel®, you do have to be able to logically set up formulas and functions so the math can be done by the software. Linear or logical thinking can be very helpful in this process. Again, it is recommended that you not skip subunits in the Unit as each subunit builds the spreadsheet for the next subunit.
Unit 4 Time Advisory
Completing this unit should take you approximately 3 hours.
☐ Subunit 4.1: 1.5 hours
☐ Subunit 4.2: 1.5 hours
Unit4 Learning Outcomes
Upon successful completion of this unit, you will be able to: - use Freeze Panes to lock rows and columns in place; - construct a logical test to evaluate the contents of a cell; and - create the IF, OR, AND, COUNTIF, AVERAGEIF(S), and SUMIF(S) functions.
4.1 Logical Functions The text explains that logical functions are used to assess the content within cells and produce custom outputs, either text or numbers. When an answer is text, you must put quotation marks both before and after the word. This signifies to the software that the answer is a word, not a mathematical computation. There is a lot of information in this subunit so go slowly and reread any section that you need to. If you are doing the work along with the text, and if anything does not match the answers that the text gives, delete the formula and start again. A misplaced comma or erroneous letter or number will make a formula wrong. I have worked through each problem and if you do them as the book instructs you should get a matching answer. Really pay attention to what you are typing or clicking and have fun!
Reading: How to Use Microsoft Excel®: “Section 3.1” Link: How to Use Microsoft Excel®: “Section 3.1”(PDF)
Instructions: Read Chapter 3: Logical and Lookup Functions through page 192 and then proceed to Section 3.1: Logical Functions, which starts on page 193. You will find the work-along Excel® file for this unit in the next resource box: Excel® Text Chapter 3.
Completing this assignment should take approximately 1.5 hours.
Activity: The Saylor Foundation’s “Excel® Text Chapter 3” Link: The Saylor Foundation’s “Excel® Text Chapter 3”
Instructions: This Excel® file is in the earlier version of Excel®, with the file extension .xls. If you have a newer version of Excel®, open it up and resave it in the current version of Excel® which has the file extension of .xlsx. If you have a newer version and leave it as an .xls you will see the text [Compatibility Mode] on the Title Bar. This will not affect the document at all. Again, the book will be showing the newer version in the pictures that accompany the text. As this chapter deals with formulas and functions, all Excel® programs write formulas in the same way and the Function dialog box is the same, so it doesn’t matter what version of Excel® you have for this unit. You will be completing the Investment Detail worksheet in this section.
Completing this activity should take approximately 1 hour.
4.2 Statistical IF Functions This subunit will introduce the COUNTIF and COUNTIFS, the AVERAGEIF and AVERAGEIFS, and the SUMIF and SUMIFS functions. These functions can be confusing because they have nested criteria but, again, go slowly and work through the logic. You will be completing the Portfolio Summary worksheet from the spreadsheet you started in Subunit 4.1.
- Reading: How to Use Microsoft Excel®: “Section 3.2”
Link: How to Use Microsoft Excel®:
Instructions: Read Chapter 3, Section 3.2: Statistical IF Functions, which starts on page 231. You will continue to use the same spreadsheet that you started above in Unit 4.1 of this class (Excel® Text Chapter 3).
Completing this assignment should take approximately 1.5.