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 ONEDRIVE 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.

IMPORTANT:

Excel has this goofy new way of displaying Column names and it makes this assignment REALLY difficult if you aren’t careful. At the top of your sheets, your columns should be named with LETTERS, NOT NUMBERS.

If you see NUMBERS across the top, like THIS:

You need to go into the File menu/tab at the top left:

Open Options:

Go on the Formulas page:

And make sure the first check mark is turned OFF. You DO NOT WANT R1C1 reference style!

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)

(Option A: THE HARDER WAY) 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

or…

(Option B: THE SMARTER WAY)

If you know what type of information a cell is going to contain, you can get Excel to do some of the work for you. You’ll notice that my percentages do not have a % symbol beside them. You could tell Excel that anything in those particular boxes is going to be a percentage by changing the Cell Format.

On the Home tab/menu, you’ll see a section called Number, and a drop box that probably says General

If you change that box to Percentage, Excel will do part of the math for you and will add the percentage symbol! It’ll automatically multiply by 100 so you don’t have to do that step.

Change the Cell Format to Percentage:

Then, you only need to do the first part of the division where you divide your mark by what it’s out of!

Type in:

and the answer should be:

You can also use that Number formatting to control how many decimal places are displayed. You can increase the number of decimal places shown with the button on the left:

Or decrease the number of decimal places shown with the button on the right. I’ll round off my numbers by clicking that one twice:

Now my overall percentage is rounded off to a whole number:

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!

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.

The numbers on this sheet should be prices, so they should look like money:

Highlight those cells:

and on the Home menu/tab, change the Format from General to Currency:

those should now look like prices:

Finished sheet:

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: