Beginning in Chapter 3, there are yellow-colored worksheets in your Chapter files that are extra preparation work we will cover in class as a part of our Guided Exercises, or we will assign you to do on your own as More Practice. These will be optional unless otherwise stated, but they are great opportunities to learn and practice your skills.

Chapter 3

CountFunctions Worksheet

We have a little bit of data here that might represent how many bookings a nail technician has at a nail salon over the course of two weeks. So we have the days and then we have the number of bookings. We’re going to answer some questions using functions and not just figuring out the answer ourselves and typing it in. It would be easy to figure out the answers right now since we have a small sample of data, but that is so you can practice and check your work. This prepares you for when you have larger sets of data. We want to use functions as much as possible when we have larger sets of data or if the data changes so that our functions do the work for us and we can trust our results are correct. We don’t have to worry about checking our answers with larger sets of data.

• First, how many days are represented?
  • o Try COUNT first – why does this not work?
  • o Retry with the COUNTA function, remember this counts non-blank cells.
• How many days have bookings?
  • o Use COUNT again.
  • o Now, you might be wondering, couldn’t we just use COUNTA for that one, too? And yes, you can but only because of how this data is set up. You won’t always be able to just use COUNTA, for example, what if instead of these cells being blank, they had something in them like “None” or “closed.” COUNTA would still count those cells and that’s not right. So, if you know you are just counting numbers, then use COUNT.
• How many days do NOT have bookings?
  • o COUNTBLANK is your friend in this one.

DateFunctions Worksheet

There is a little table here in column G through column Q to help explain how dates are numbers in Excel. When Excel was programmed, it was decided that day 1 would be 1/1/1900. Each day since then has just been another number higher than that.

In cell B3, we want today’s date to be displayed. BUT, I want today’s date to always be displayed no matter when this file is opened and we don’t want to type in today’s date because it won’t change. Instead, we use the TODAY function. The TODAY function always displays the current date.

Here in Column A, we have some of our K160 and K201 TAs birthdays. Remember, dates are numbers, so these are the number of days that had passed since 1/1/1900 on the day you were born. But, they make no sense to us. In Excel, the numbers associated with dates are serial numbers.

• Click in cell A6, then on your keyboard, press Ctrl, Shift, and the down arrow. Now the column is selected.
• Format as Short Date.

Now we need to calculate the difference between dates, but not just using math. The function we use for this is called the DateDif function.

So, what about Weeks – you can’t put in “W” it doesn’t work.

There are always 7 days in a week. So, if we have the number of days and every 7 days is a week, we can divide to get the number of weeks. Our TAs are not a whole number of weeks old. There’s a bunch of decimals. That will happen when you divide, especially by 7.

• Try to use ROUND in front of this DATEDIF, to 0 (zero) decimal places).
• Change that to use INT().
• Fill that down, and now you have accurate data.
• Decrease the decimals so you see none (just whole numbers).

Remember, DATEDIF + WEEKS = INT (not ROUND)

Chapter 4

Conditional Aggregate functions and Mixed Cell referencing.

Well, you already know half of this, you just might not realize it. To aggregate means to summarize and we know how to do this with functions like SUM, MAX, AVERAGE.

The conditional part just means that you only want to summarize data that meets certain conditions. We call that criteria. So, add up the values IF they meet a certain condition or conditions.

ConditionalAggrigates_Video worksheet

What if we want to know the answer to this question: What was the revenue for the month of February?

SUMIFS using the “Sales Revenue” column.

How many shirts were sold in February?

COUNTIFS using any column with content.

MixedReferencing_Video worksheet

Remember our Absolute Referencing technique using the F4 key? We also called this “locking”, especially when it comes to what we call “island cells”. But what if we only want to lock-in the row or column we’re looking at? Let’s walk through this worksheet together.

Chapter 5

IF_Video&Practice worksheet

Let’s take a look at some scenarios where we may need to make a decision, or rather have Excel make a decision for us. We can do this by testing some data we already have, and then having Excel make a decision based on the test result.

So IF my test is TRUE (or it passes), then we do something. IF my test is FALSE (fails), then we do an alternate thing:

IF (TEST, SOMETHING_IF_TRUE, SOMETHING_IF_FALSE)

or

IF (A1 + B1 = 4, “Yay this is true”, “Oops, this is false”)

If the sum of A1 and B1 is in fact 4, then we output the “Yay…” phrase. Otherwise, they do NOT sum to 4 and then we give the “Oops…” phrase.

Chapter 6

Now we want to be able to lookup data from one part of a worksheet to another, or anywhere in the entire Excel file. If we have a table of sales figures in one sheet, we can lookup (or find) certain lines of data depending on something else we know, like a date in this case.

We can look data up going vertically (down a list of scrolling data, like transaction information or a grade report) or horizontally (going across like the day-count explanation from Chapter 3). So which type of lookup would you use in the Tables worksheet in Chapter 6?

VLOOKUP vs HLOOKUP worksheet

The basic syntax of VLOOKUP:

VLOOKUP ( what we lookup, where we find it, what column value we want back, and if we’re looking exactly or approximately )

or from our first example in C4

VLOOKUP( C3, B8:D12, 2, FALSE )

Note that VLOOKUP needs to always start in the left-most column. The value in C3 above MUST be found in column B.

The basic syntax of HLOOKUP:

HLOOKUP( what we lookup, where we find it, what row value e want back, and if we’re looking exactly or approximately )

Or from our example in C18

HLOOKUP( C17, C21:G23, 2, FALSE )

Note that HLOOKUP needs to always start in the top-most row. The value in C17 above MUST be found in row 21.

Chapter 8

Here is a basic summary of some of our new functions to manipulate text data. Let’s open the IndianaFightSong worksheet from this chapter and see if we can dig into why we would use these for each item in column F.

• LEN returns the number of characters in a string of text.
• FIND or SEARCH returns the position number of the first character in a string of text you’re looking for within some larger string of text. FIND is case-sensitive; SEARCH is not.
• LEFT, RIGHT, and MID return some number of characters from the beginning, end, or middle of a string of text.
• CONCATENATE combines text elements to create a single string of text. The & symbol also works.