# Chapter 3 – Formulas, Functions, Logical and Lookup Functions

Excel workbooks are designed to allow you to create useful and complex calculations. In addition to doing arithmetic, you can use Excel to look up data, and to display results based on logical conditions. We will also look at ways to highlight specific results. These skills will be demonstrated in the context of a typical gradebook spreadsheet that contains the results for an imaginary Excel class.

In this chapter, we will:

- Use the
**Quick Analysis Tool**to find the Total Points for all students and Points Possible.

(**Note for Mac Users**: the Quick Analysis Tool is not available with Excel for Mac. We have alternate steps for Mac Users) - Write a division formula to find the Percentage for each student, using an absolute reference to the Total Points Possible.
- Write an
**IF Function**to determine Pass/Fail – passing is 70% or higher. - Write a
**VLOOKUP**to determine the Letter Grade using the Letter Grades scale. - Use the
**TODAY**function to insert the current date. - Review common
**Error Messages**using Smart Lookup to get definitions of some of the terms in your spreadsheet. - Apply
**Data Bars**to the Total Points values. - Apply
**Conditional Formatting**to the Percentage, Pass/Fail, and Letter Grade columns. **Printing Review**– Change to Landscape, Scale to Fit Columns on One Page and Set Print Area.

**Figure 3-1** shows the completed workbook that will be demonstrated in this chapter. Notice the techniques used in columns O and R that highlight the results of your calculations. Notice, also that there are more numbers on this version of the file than you will see in your original data file. These are all completed using Excel calculations.

Figure 3.1 Completed Gradebook Worksheet

# Attribution

Chapter 3 – Formulas, Functions, Logical and Lookup Functions by Noreen Brown, Mary Schatz, and Art Schneider, Portland Community College, is licensed under CC BY 4.0