COMSC-101 OFFICE SOFTWARE LAB

Excel Lesson 4:
More Functions
The learning objectives for Excel Lesson 4 are to learn about and practice:

I. TODAY and NOW functions

II. IF and COUNTIF functions
III. SUMIF and LOOKUP functions
______________________________________________________________
To complete this lesson, you will begin with the same workbook you used in Excel Lesson
3.

STEP 1. DO THIS:
 Log in to your Office 365 account, select OneDrive, and navigate to your excel
folder.
 Click on the file named Last_First_excel_lesson_3 to open the file in Excel Online.
 Click on the FILE tab and Save As (Save a copy online) the workbook as
Last_First_excel_lesson_4

In Excel Lesson 3, you added data and formulas, and created charts, on the Word
worksheet, and updated the Summary worksheet and formatted existing data as a
table.

In this lesson, you will be learning some new functions and creating lookup tables as
well as adding numbers, formulas, and functions you’ve already learned.

In the next step, you can add points that you have earned from the Canvas
gradebook, or you can make up numbers for this exercise. If you choose to make up
numbers, they should be in the actual range of possible points: a maximum of 20 points
for each project, 5 points for each quiz, and 50 points each for the exam project and
quiz.

STEP 2. DO THIS:
 Click on the PowerPoint tab to open the PowerPoint worksheet.
 Move the row with PowerPoint Exam below the Total, Count, Average,
Maximum, and Minimum rows.
If you forgot how to do this, re-read the instructions from lesson 3 where you moved
the row with the Word Exam below the Minimum row on the Word worksheet.

Excel Lesson 4.docx Page 1 of 10

 COMSC-101 OFFICE SOFTWARE LAB

Now your PowerPoint worksheet should look like

this:

STEP 3. DO THIS:
 Enter points earned for PowerPoint
lessons 1-8 in cells B2:C6, and the
PowerPoint Exam in B12.
You can enter the numbers used in the
example screenshot below, use your own or
make them up.

Remember that Project lessons are worth a maximum of 20 points, and Project
quizzes are worth a maximum of 5 points. Vary the points a little. Do not put 20
and 5 in every column for this exercise. PowerPoint Exam project and quiz are
each a maximum of 50 points.

Enter the same formulas and functions in this worksheet that you entered in
the Office365 and Word worksheets.

 In cell D2, type: =B2+C2

 Use auto-fill to fill cells D3:D6 from D2
 Use the AutoSum button to complete the Total, Count, Average,
Maximum, and Minimum, respectively, for cells B7, B8, B9, B10, and
B11
 Use auto-fill to fill C7:C11 from B7:B11
 In cell D12, type: =B12+C12

Now your PowerPoint worksheet will look

filled in like this, but your numbers will be
different unless you entered the same point
values for your lessons and exam.

TODAY and NOW functions

TODAY and NOW functions are like date and
time stamps that you may want to use in your
workbooks. There are no arguments to enter for
the =TODAY() or =NOW() functions.

Both of these date functions draw their data from the computer's built-in clock.
Remember, computers store the date as a number representing the number of full
days since midnight January 1, 1900 plus the number of hours, minutes, and seconds

Excel Lesson 4.docx Page 2 of 10

 COMSC-101 OFFICE SOFTWARE LAB

for the current day.

The TODAY function returns the date. The NOW function returns the date and time.
These functions are typically added to a worksheet to identify the date and time a
worksheet was created or updated on a printed copy of the sheet. For a worksheet like
PowerPoint, adding the TODAY function makes sense because nothing is likely to
change from one hour to the next. If your worksheet contained data that changed
more rapidly, like inventory or financial data for example, you might want to use the
NOW function so you would know exactly what time it was when the worksheet was
printed.

STEP 4. DO THIS:
 In cell A14, type: Date
 Press Enter.
 In cell A15, type: =TODAY()
 Press Enter.

The current date will now be displayed in this cell as

shown on the right. The date will be the current
date, not the date in the screenshot. Notice that
the TODAY() function is displayed in the formula bar
when cell A15 is selected.

IF and COUNTIF functions

You have already been introduced to some of the most used functions in Excel,
including SUM, COUNT, AVERAGE, MAX, and MIN. Here are two more: IF and COUNTIF.
The IF function checks whether a condition has been met, and returns one value if
TRUE, and another value if FALSE. The COUNTIF function counts the number of cells
within a range that meet the given condition.

In the next step, you are going to add a new column of data to your PowerPoint
worksheet using these two functions. The column will display text indicating whether the
Total points earned for each lesson (shown in column D, D2:D6 specifically) is above or
below the Average number of points earned per lesson (shown in D9). Then, below that
list, you will display the number of lessons in which your point total was above average,
and the number below average.

Functions like this are often used in spreadsheets to highlight information in a way that is
difficult to see just looking at the numbers.

STEP 5. DO THIS:
 In cell D9, enter the Average function for the range D2:D6
 In cell E1, type: Above or Below Average

Excel Lesson 4.docx Page 3 of 10

 COMSC-101 OFFICE SOFTWARE LAB

 Press Enter
 Select cell E1 and apply Bold text formatting to the cell.
 Re-size column E so the column is the width of the text just entered.
 In cell E2, type: =IF((D2>$D$9),”Above”,”Below”)
In words, this formula says, “If the total for this lesson is greater than the average,
then display the word ‘Above’, and if not, display the word ‘Below’.”
 Select cell E2 and click on the Center button to center the text in the cell.
 With cell E2 still selected, use the fill-handle to auto-fill the rest of the
rows in the column down to row 6.
Now the word ‘Above’ or the word ‘Below’ should be displayed in the cell in the
range E2:E6.

To count the number of lessons above and below the average, you will use the
COUNTIF function.
STEP 6. DO THIS:
 In cell E7, type: Number Above Average
 In cell E8, type: Number Below Average
 Select E7:E8 and apply Bold text formatting.
 In cell F7, type:
=COUNTIF(E2:E6,”Above”)
 In cell F8, type:
=COUNTIF(E2:E6,”Below”)

Your PowerPoint worksheet

should now look like the
screenshot on the right, but
your numbers and rows with
Above or Below may be

Excel Lesson 4.docx Page 4 of 10

 COMSC-101 OFFICE SOFTWARE LAB

different.

SUMIF and LOOKUP functions

The SUMIF function adds the values in cells specified by a given condition or criterion.
The LOOKUP function in Excel is used to search one column of data and find data in a
corresponding row. You are going to learn how to use SUMIF and LOOKUP functions on
your Summary worksheet in order to determine your current lab grade.

You don’t have to wait for your instructor to tell you your lab grade if you know these
three things: (1) the total points you have earned at any point in time, (2) the total
possible points that could have been earned at the same point in time, and the
grading scale.

STEP 9. DO THIS:
 Click on the Summary tab

Next you must change how the exam points are displayed on the Summary
worksheet. You are going to insert three new rows beneath the current Exams row
and enter the individual exam names in the new rows.

 Select rows 11:13, right-click on the selected rows, and choose Insert Rows.
 In cell A10, type over the current entry, Exams, so it reads: Word Exam
 In cell A11, type: PowerPoint Exam
 In cell A12, type: Python Exam
 In cell A13, edit Exams to: Excel Exam

These same new rows for individual exams must also be added to the row
labels for Percent of Points Earned.
 Select rows 11:13, right-click, and choose Copy.
 Select row 23, right-click and choose Insert Copied Cells
 In cell A22, type over the current entry, Exams, so it reads: Word Exam
 Auto-fill cells B22:B25 from B21

Now you are going to change the value for Number of Projects in cell B10 to 1,
and auto-fill that as the value for each exam, and make corresponding
changes in the Possible Points and Total Possible Points columns for each exam.
 In cell B10, type: 1
 Auto-fill the value 1 in B10 to cells B11:B13
 Auto-fill the value 100 in C10 to cells C11:C13
 Auto-fill the formula in D10 to cells D10:D13

The Totals in row 14 must be changed also.

 Use AutoSum to recalculate the total in B14.

Excel Lesson 4.docx Page 5 of 10

 COMSC-101 OFFICE SOFTWARE LAB

 Delete the total in C14.

 Use AutoSum to recalculate the total in D14.

The totals shown in B14 and D14 should be the same as they were before: 28 in
B14 and 1000 in D14. The total that was in C14 was deleted because it was
meaningless and isn’t needed. This is how the top of your Summary worksheet
should look after making the changes above:

Finally, it is time to employ the SUMIF and LOOKUP functions so you can monitor your
current grade in the lab. You are going to use these two functions to display in one
“grade box” your
current total points earned, the current total possible points, your current
percentage score, and the letter grade that corresponds to the percentage
score.
Unless you have added additional data on your own, your current Summary
worksheet should only show Total Topic Points Earned for the Office 365 and
Word lessons. In this lesson, you are going to build the grade box before
updating the Summary worksheet with PowerPoint lesson points and exam
points so you can see the grade box update as you add those points later.
STEP 10. DO THIS:
 In cell D17, type: Current Earned
 In cell D18, type: Current Possible
 In cell D19, type: Current Percent
 In cell D20, type: Current Grade
 Select column D and use the Format button to Auto Fit Column Width
 In cell E17, type: =SUM(E5:E13)
In cell E18, type: =SUMIF(E5:E13,">0",D5:D13)
 In cell E19, type: =E17/E18
 Select cell E19, click on the % button in the Number group on the HOME

Excel Lesson 4.docx Page 6 of 10

 COMSC-101 OFFICE SOFTWARE LAB

tab.
 With cell E19 still selected, and if necessary click the Decrease Decimal
button twice to remove the two decimal places and display the
percentage as a whole number.
The first four entries above are of course just labels for the values you want to
compute. The SUM function in cell E17 simply says, in words, to sum all the values
in the range E5:E13. This is the same SUM function you’ve used before by
clicking on the AutoSum button.
The SUMIF function in cell E18 is new. In words, it says to sum all the values in the
range D5:D13 for which there are corresponding values in the range E5:E13 that
are greater than 0.
The formula in cell E19 is just like what you entered for Percent of Points Earned.
It just divides the total current points earned by the total current points possible,
as displayed in the two cells above it.
To add the Current Grade as a letter grade from A to F, you will enter the
grading scale somewhere in the workbook and then use the LOOKUP function
to display the letter grade that corresponds to the Current Percent in your
grade box.

The grading scale that serves as the lookup table for this function can be
placed anywhere it can be easily referenced by the LOOKUP function. In this
lesson, you will create that lookup table on the Summary worksheet down
below your Percent of Points Earned values.
STEP 11. DO THIS:
 In cell A27, type: Grading Scale
 Select cell A27 and apply Bold text formatting to the label
 In cell A28, type: F
 In cell A29, type: D
 In cell A30, type: C
 In cell A31, type: B
 In cell A32, type: A

Here is an opportunity to practice inserting percentage values directly in the

corresponding cells as you learned to do in the Excel Lesson 1:
 In cell B28, type: 0%
 In cell B29, type 60%
 In cell B30, type 70%
 In cell B31, type 80%

Excel Lesson 4.docx Page 7 of 10

 COMSC-101 OFFICE SOFTWARE LAB

 In cell B32, type 90%

 Center the contents of cells A27:B32
You have completed the lookup table to be used with your LOOKUP function.
Now enter the LOOKUP function in cell E20 to the right of the Current Grade
label.
 In cell E20, type: =LOOKUP(E19,B28:B32,A28:A32)

In words, this function says to find the percentage in the Grading Scale lookup
table that matches the value displayed as your Current Percent in cell E19, and
display the corresponding letter grade in this cell (E20, the cell where the
LOOKUP function was entered).
The “match” of percentages is a comparison of your Current Percent with a
“cutoff” percentage in the lookup table, not an exact match, obviously. Any
Current Percent of 90% or greater matches the cutoff for an A, any Current
Percent of at least 80% but less than 90% matches the cutoff for a B, and so on.
Complete this step by applying Bold text formatting to the Current Grade label
and the letter grade, and create the box by applying an outside border to the
cells displaying the labels and values for Current Earned to Current Grade.
 Center the contents of cell E20
 Select the cell range D20:E20 and click the Bold button in the Font group
on the HOME tab.
 Select the cell range D17:E20 and click the arrow on the right side of the
Border button in the same Font group, and click Outside Border

Assuming you haven’t made any more updates to your Summary worksheet
than the Total Points Earned for Office 365 and Word Online that you entered in
Excel Lessons 2 and 3, your Summary worksheet should now look like the
screenshot below.

Excel Lesson 4.docx Page 8 of 10

 COMSC-101 OFFICE SOFTWARE LAB

Remember, your values may be different depending on the values you entered
in your detail worksheets.

Now you can watch the grade box change as you update Total Points earned
for your PowerPoint lessons and your Word and PowerPoint exams!
STEP 10. DO THIS:

 On the PowerPoint worksheet, enter the Sum function in cell D7 for the
range D2:D6.
 On the Summary worksheet, in cell E7, enter the formula to display the
contents of cell D7 from the PowerPoint worksheet.

Excel Lesson 4.docx Page 9 of 10

 COMSC-101 OFFICE SOFTWARE LAB

Observe that cell B19 automatically updated with your new entry in E7 as well
as the values displayed in your grade box. Continue:
 In cell E10, enter the formula to display the contents of Word!D11
 In cell E11, enter the formula to display the contents of PowerPoint!D12
To complete the update of your Summary worksheet:
 Copy cell F6
 Paste into F7
 Paste again into F10 and F11
You are done! Here is what the updated Summery worksheet looks like:

Excel Lesson 4.docx Page 10 of 10