Excel Chapter 3 – Building More Complex Formulas

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.
  • 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.
  • 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 for the Grades sheet in your Chapter data file.

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

CH3 Data.xlsx is gradebook worksheet. Range A1:R1 merged into one cell title "CAS 170 Grades". Range A2:R2 also merged into one cell. Student Names in Column A5:24 (18 students) titled "Student Name" (bold, A4). Columns B4:R4 titled successively B4-D4 CH1, CH2, CH3, E4 Test 1, F4:H4 CH4, CH5, CH6, I4 Test 2, J4:L4 CH7, CH8, CH9, M4 Test 3, N4 Final Exam, O4 Total Points, P4 Percentage, Q4 Pass/Fail, and R4 Letter Grade (all bold, underline) Chapters and Test score entered for every student through Final Exam, bold underline after final student in Row 24. A25 Points Possible (bold). A27 "Letter Grades" and A:28-32 show percent range successively 0,60,70,80,90%. B:28-32 letter grade scale successively F,D,C,B,A.

The other examples for this chapter will be based on subsets of data files you continue to work with throughout the semester from the U.S. Department of Education and the U.S. Energy Information Administration.

 

Attribution

Chapter 3 – Formulas, Functions, Logical and Lookup Functions by Noreen Brown and Mary Schatz, PCC.EDU, is licensed under CC BY 4.0

License

Icon for the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Business Computer Information Systems Copyright © 2020 by Emese Felvegi; Barbara Lave; Diane Shingledecker; Julie Romey; Noreen Brown; Mary Schatz; OpenStax; Saylor Academy; University of Minnesota Libraries; and Robert McCarn is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.