Excel Formulas – part 2

 

Switch to the COUNT tab

Excel can count for you any cells that have numbers in them. On the COUNT sheet, we’ve got a sample of a survey. Every time someone chooses a favourite flavour, we put a 1 in the box, and then we’re going to ask Excel to count those up for us. In this example, it wouldn’t be hard to count them yourself, but again, if the answers changed for whatever reason, now Excel will keep track of those numbers as they change.

First, pretty up your sheet:

The formula we want is called COUNT

After the formula, you have to put your cell choices in brackets, so type an open bracket)

Then go select the cells you want to count. For Chocolate ice cream, we’d choose B4 and drag down to B19

Close the bracket to finish the formula

Answer:

Since we’re applying the same formula to other nearby cells, Fill Handles are your friend!

 

Your finished formulas will look like this:

Switch to the COUNTIF sheet

Pretty it up!

The COUNT formula we used last time only works with numbers. This time we’re going to count cells with text. The formula is written down below in cell A22

You can write it out or use the miracle of Copy and Paste

But we need to modify the formula as it stands, because right now it won’t do anything. We need to tell it where to look and what to look for.

 

The first part of the formula inside the brackets tells it where to look, so click right inside the brackets

Then either type in the cells you want to check or just click and drag to select

The second part tells the formula what to look for.  In this case, we want to look for the letter c, so inside the quotation marks, type a c

The Vanilla column is basically the same, except it looks in column C and looks for a letter v

And strawberry looks in column D and looks for an s

What if we want to count all of the cells that have a letter in them (or any text, really)? Well, just stick with that asterisk. It’s a “wildcard” that looks for any text at all

So here you can select columns B, C, and D to look for c, v, or s

Your finished sheet should look something like this

With the formulas, it looks like this:

Switch to the CONCAT sheet

Here you will learn to concatenate, or join two things together.

Let’s say for some reason we want to combine whatever is in column A (Name) with whatever is in column B (Favourite) and have column C display them both.

Your formula starter is in cell A26

In cell C4, we want a formula that looks like this:

That says that we want to take whatever is in A4, adds a space ” ” then combines it with B4. Separate the parts with commas.

And your result should look like this:

A4, space, B4

Our old friend the Fill Handle would come in really handy here

Now let’s fill in some totals down below

Start with B22 and count how many cells have text to get our total number of responses

Move down below and count how many cells contain the word chocolate

Then vanilla

and strawberry

Your finished sheet will look like this:

And the formulas look like this:

 

Move on to the SORT sheet to learn about one of Excel’s other really valuable and important functions

Sometimes you have a list of data and you want to organize it. Right now, I have a list of all of the students in the school and it’s organized/sorted by first name

But what if I want to see class lists? Well, all you have to do is go into the Data menu/tab

and click Sort

A box will pop up and it has a few important options

First, make sure you check off My data has headers. This means that we don’t want to sort the first row, so we want to leave it alone

It usually comes up with a suggested sort order, but you may have to click Add Level

The first way we want to sort is by Class/Gender

That will put all of the classes together and the girls and boys together, but we also want them to be sorted by their first name. So the data should be sorted by Class/Gender, Then by Name

Mine came out like this (not sure why)

So I want to select and delete those empty rows

Now we have a list that’s sorted the way we want!

 

The final sheet, Assessment is one that you’ll have to complete on your own. It will use the data from the SORT sheet to come up with some totals for us.

NOTE: If you downloaded the file early, you may have a sheet that isn’t quite complete… OOPS! There’s some important information included below that you may be missing

This final sheet is one that I’d like you to do on your own. I’m not going to show you how to do it all. A few important tips, though:

You’ll use the data on the SORT sheet for these formulas. Just like always, you can select cells to include in your formulas, even if they’re on another sheet.

Use the formulas you learned before!

COUNTIF the number of students in each class

COUNTIF the number of girls and the number of boys

ADD (SUM) those up to get totals for girls, boys, and overall for each grade and the entire school.

Figure out what percentage of each grade and the entire school is girls or boys

Percentage is the number of girls or boys divided by the total number of students for the grade, times 100

The numbers below should be accurate in your answers, but you have to get the formula right to get those numbers.

Good luck!

Drop this off in your OneDrive folder, inside the shared folder with your name, when you’re done!

Tell Mr. Robson what's on your mind!