Using Functions to Simplify Data

Quick reference

• 00:04 The real power of Excel lies in how quickly it helps us to simplify large
• 00:08 amounts of data, to help us gain a better insight to make decisions.
• 00:13 At our local high school,
• 00:14 we have a group of 10 students who are on the honor roll this year.
• 00:18 The school principal wants to use this group's exam results
• 00:22 as a benchmark to decide if the coursework was set at the right level of difficulty.
• 00:27 Were the exams too hard for our top performers, or were they too easy?
• 00:33 So let's start by checking how many of these students achieved the 70% pass mark
• 00:38 and how many didn't.
• 00:40 First, let's go to the bottom of our data set and
• 00:42 type the word passed in one row, and failed in another row.
• 00:48 In each column, we'll want to get the respective number for each subject.
• 00:52 And we know that if we get at least one result first,
• 00:55 we can always copy the formula to the other cells later.
• 00:59 Now we don't know the name of the function.
• 01:01 So let's search for it.
• 01:03 What do we want our function to do for us?
• 01:06 We want to count the number of cells that are 70% or higher.
• 01:09 And below that, we'll want to count the number of cells that are lower than 70%.
• 01:14 So we're counting cells based on a criteria.
• 01:18 Let's click on our Insert Function shortcut right here.
• 01:21 And within the search box, let's type cells that meet criteria.
• 01:27 We click Go, and there's one function recommended.
• 01:31 It says that this function counts the number
• 01:34 of cells within a range that meet the given condition.
• 01:37 The COUNTIF function sounds great, that's what we want.
• 01:40 Now sometimes the language we use in our search
• 01:42 won't return a function that satisfies our need.
• 01:46 When that happens, we can use the drop down list here to search for
• 01:50 a function by its category.
• 01:53 That same list of categories also appears here by clicking on the Formulas tab and
• 01:59 looking at the Function Library.
• 02:01 Within each category, a list of functions appears.
• 02:05 And if we remember that function names are meant to be as descriptive as possible
• 02:09 based on what they do, that helps to narrow down our choices.
• 02:13 So let's get back to our students.
• 02:15 We did like the COUNTIF Function.
• 02:17 So we go back to the Insert Function, and we can
• 02:22 go to most recently used and select the function, we liked our COUNTIF Function.
• 02:28 So we click OK and a little wizard comes up.
• 02:31 We'll use that to supply the needed arguments for the function.
• 02:35 For this function two arguments are required.
• 02:37 The range of cells that we're concerned with, that is, where do we want to look?
• 02:42 And the criteria that they should satisfy.
• 02:45 That is what do we want to look for?
• 02:46 Where we want to look is right here, B5 to be 14 for our first subject.
• 02:52 We highlight that right on the worksheet, and it's populated within the wizard.
• 02:57 And our criteria, we want to look for
• 02:59 anything that's greater than or equal to 70%.
• 03:04 We click OK.
• 03:05 And it says eight students passed the Visual Arts.
• 03:09 We also look at the formula bar and
• 03:11 see how the formula that we typed in from the wizard is represented.
• 03:15 Notice the double quotation marks around greater than or equal to 70%.
• 03:20 And how many students didn't pass the Visual Arts?
• 03:24 We know that there are 10 students in all, so the answer is going to be 2, but
• 03:28 if we were working with larger numbers,
• 03:30 we wouldn't want to run the risk of a manual calculation.
• 03:33 So we'd use a formula.
• 03:35 Let's practice doing that.
• 03:37 We copy our formula to the cell below, and
• 03:41 we adjust our criteria because we want students who got less than 70%.
• 03:46 We type the less than sign, and we hit Enter.
• 03:50 But instead of seeing that 2 students failed, it says only 1 failed, why?
• 03:56 Remember that cell references within formulas are relative,
• 03:59 unless stated otherwise.
• 04:01 So when we copy the formula from B16 to B17,
• 04:06 it shifted the student range down by 1.
• 04:09 Let's click on F2 to get inside that cell, and we see what happened.
• 04:14 This student was excluded, and
• 04:17 a blank row was included because the range was shifted down.
• 04:20 Now we don't want that to happen.
• 04:22 We want our row numbers to stay put throughout our calculation.
• 04:27 What we can do is to pay the row numbers to stay put.
• 04:31 Recall that making an absolute cell reference.
• 04:34 How do we go about doing that?
• 04:37 We go to our original formula, and we put $1 sign before those
• 04:42 references that we don't want shifted when we copy our formula.
• 04:46 So now let's copy this formula to this row and
• 04:50 adjust our criteria again to read less than 70%.
• 04:55 And now it shows the correct number, 2 students failed.
• 05:00 This looks good.
• 05:01 So we'll copy our formula to the remaining subjects and
• 05:04 we have all the figures we need.
• 05:07 Let's say these two sets of numbers aren't enough to provide the answers
• 05:10 that we're looking for.
• 05:11 The principal thing's that if she knows how many students did really well, and
• 05:16 how many did really poorly,
• 05:18 she'd be in a better position to determine which subject tests require a closer look.
• 05:23 So let's check how many students got 85% or higher.
• 05:27 Let's put a label Excelled here.
• 05:30 And what about those who struggled?
• 05:32 Let's say they got less than 50%.
• 05:35 Let's put them in this row here, Struggled, and let's copy our formula.
• 05:42 Adjust the criteria, Excelled is greater than or equal to 85%.
• 05:50 And for those who struggled, we copy and they would be less than 50%.
• 05:57 We make sure that everything is within double quotes.
• 06:02 And we copy to the end.
• 06:05 Now we have the numbers that we want and
• 06:07 we can present this information as a tool for further analysis.

Lesson notes are only available for subscribers.