Excel Formulas

Excel is primarily used for mathematical functions and keeping track of numbers. You can make graphs & charts, analyze data, and do all kinds of mathematical equations. That’s what we’ll start to do.

You’ll need to download and open this file: formulas

SAVE that file into your DOCUMENTS folder!

You can either watch the video and follow the directions or scroll down and read/look at what to do. If you want to watch the video, you NEED HEADPHONES and should only watch a bit at a time. Watch until I do something, pause the video, and go do what I did. Come back and watch some more after you’re done or review if you’re lost.

If you look at the bottom of the screen, you will see different pages or SHEETS

Each sheet asks you to do different calculations.

You should RESIZE your COLUMNS to properly fit the information contained in each one

You should also add a splash of colour to make your sheet look nice and to make it easier to read

Start with the CALCULATIONS sheet on the left. When you are done, it should look kinda sorta like this:

The cells in yellow contain the answers, but I did not figure out the answers on my own (too hard for me), I let Excel do the math for me!

To get Excel to do math, you always start your forumla with an equal sign =

Then you select which cells you want to perform. I don’t just want it to add 10 + 5, I want to add whatever is in cell B2 and whatever is in cell C2 to get the answer in D2

So I would type an equal sign then click on B2

Then add a plus sign and click on C2

The cool thing now is that if you change either of the numbers, the answer will change too

For the next one, type an equal sign, then click on the number in B3 (you can also type it in) then a minus sign, then click on or type C3, then enter

Next, you need to know that the sign for multiplication is an asterisk *

And the sign for division is a slash

For the next section, those are some complicated formulas! Nobody is going to figure those out in their heads, so a little help from the technology sure comes in handy. Fill in the formulas EXACTLY as they are written in the first column (boy, if only there was a way to use the formulas already written there instead of recreating them ourselves…)

You get the idea. Fill in the last two yourself. Your answers should look like this:

Before you move on to the next sheet, pretty up your page! You should always make your titles and headings stand out from the data so your page looks better and is easier to read. Use fonts, sizes, text colours, shading, bold/italics/underline, etc. to make your page look a lot better.

 

Switch to the next sheet, which is called Average

Your finished product will look something like this:

Start by figuring out the Term 1 average for Math (D2)

To figure out an average, you take your mark, divide by whatever it’s out of, then multiply by 100. In this case, that’s:

=B2/C2*100

You’re going to use that same formula for all of your subjects, so you could go and type that in for each subject….

or…

You can use one of the coolest features of Excel and save yourself a LOT of work!

 

Any time you type in a Formula (or anything, really) there’s a tiny little square in the bottom right corner. That’s called a FILL HANDLE

If you put your cursor on that square, it changes into a little cross

You can take that formula and apply it to other cells by dragging that FILL HANDLE down!

Excel takes the formula and changes it for you so that it calculates each row separately!

You’ll notice that your formulas are calculated and rounded off to two decimals places. But say you don’t want that…

Select column D by clicking on it

Make sure you’re looking at the HOME menu/tab

 

In the “Number” section, there’s a little tab that probably says General right now

Click the little triangle next to that and choose Number

Two ways to do the next step:

Underneath that Number section, look for the tiny little button at the bottom right and click it

OR

Right click on the column letter (D) and choose Format Cells

Make sure that the Category is Number

Right now you have two decimal places

but you can easily adjust that down to 0

OK? Then press OK!

Now your averages look a little better

Do those same steps for the other terms!

Your formulas will look like this:

Now we need to figure out the Average for the year for each class. Start with Math (K2). Excel has a bunch of predetermined formulas already within it. You can type the equal sign then start typing in Average and it’ll pop up a list of formulas for you to choose from. Double click AVERAGE

Then you need to just go click on Term 1% (D2), insert a COMMA, then click Term 2% (G2), then another COMMA, then click Term 3% (J2)

OR click Term 1% (D2), HOLD DOWN CTRL then click Term 2% (G2), then click Term 3% (J2)

Use that FILL HANDLE to apply that formula to your other classes…

Now we need to calculate our Overall Average (B11)

Click in that cell and start typing Average… double click the right formula.

This time, you can click on your Math average (K2) and DRAG down to your Practical Arts average (K8)

Hit enter and bingo!

 

Next you can go to the FILL sheet

Your finished product will look kinda sorta like this:

The formula for calculating Heat is provided at the bottom (A10)

Click in D2, type equal sign, then start creating the formula

Again use the FILL HANDLE to make life easier for yourself

Pretty up your page and you’re done!

Switch your sheet to Max. & Min.

This one asks you to compare the prices of some fictional computer packages.

Finished product:

Start with the Price Before Tax of the first package (D2)

Here’s another cool trick. Excel has some formulas that you can apply with just the click of a button. On the HOME tab, look for The AutoSum button at the far right

Click it! It’ll try to figure out what you want to add.

Unfortunately, it sometimes guesses wrong, so carefully check and make sure that it’s adding up all of the right numbers! (A2 + B2 + C2)

If it’s right, Fill Handles are your friends!

Now we have to calculate the tax. PST is 7% of the price, so we take the complete package price and multiply by .07 (=D2*.07)

Fun with Fill Handles!

GST is 5% (D2*.05)

Then we have to add the package price + PST + GST to get the Total Cost. Again, the AutoSum button comes in handy, but it’s sometimes wrong, so you might have to adjust the formula. Wrong:

Right!:

Fill it on down!

Down below, we’re going to figure out what the average price is for the packages

And figure out which one is highest and which is lowest

For the Highest Price, we want a formula called MAX:

And then make sure that it looks in the Total Cost column:

The opposite of MAX is MIN

 

SAVE your work!

 

Part 2 will go over the rest of the sheets, but I’d bet that you can figure most of them out yourself!

 

 

Tell Mr. Robson what's on your mind!