Basic Math

Quick reference

• 00:05 One of the reasons we use Excel so extensively in the real world is
• 00:09 because it's one of the world's greatest analytical tools.
• 00:13 And allows us to do a lot of mathematical calculations and
• 00:15 build business intelligence solutions out of it.
• 00:19 The challenge is of course that we've got to figure out how to work with formulas in
• 00:22 order to make that happen.
• 00:24 Since Excel has a great calculation engine in it,
• 00:27 we need to understand what mathematical symbols we can use.
• 00:30 And those symbols are, as you probably expect, going to be + for
• 00:34 addition,- for subtraction.
• 00:36 They're going to be the * for multiplication,
• 00:41 the / for division, and if you need to raise a number to the power,
• 00:46 you need the little hat icon here that is Shift+6 on the North American keyboard.
• 00:51 And I believe that's similar in the rest of the world,
• 00:53 but Shift+6 will give you that little hat.
• 00:56 That's how we raise a number to the power, one number on one side to the power of and
• 00:59 then the next number.
• 01:01 So these are the symbols that we use for our math.
• 01:04 Now, if we wanted to use those to say, take the numbers in our table and
• 01:09 calculate the worth for our individual products.
• 01:14 What we'd really like to do is we'd like to set cell D6 =,
• 01:20 whoops, hang on, = 56 x 8.99.
• 01:25 Any time we do a calculation, we always start with equals and
• 01:29 this is gonna set the cell equal to that amount, which is 503.44.
• 01:34 But there's a challenge with this because we'd really like to take this, copy it,
• 01:38 paste it into the cell and have it work for the numbers on the left.
• 01:42 And right now obviously it won't because, it's hard coded to these values.
• 01:47 So wouldn't it be nice if we could actually say equals this cell times
• 01:51 this cell.
• 01:52 So let's give that a shot.
• 01:57 Let's say equals 56 in B5 times the 8.99 in C5.
• 02:03 And when we hit Enter,
• 02:05 you'll notice that the calculation wrings true with what we calculated before.
• 02:09 That's kinda cool.
• 02:09 But this has a benefit because now we can copy this and
• 02:14 we can select our cells here and right click and paste.
• 02:19 And you'll see that the formulas have actually run all the way down the table
• 02:23 and are working nicely for us.
• 02:24 This one in fact is pointed to these two cells.
• 02:27 And we can prove it out by saying, = 84 x 7.97,
• 02:32 I'll just move that cursor out of the way there, and say Enter.
• 02:38 And indeed, it rings true at 669.48.
• 02:43 We're gonna talk in future videos about why formulas extend this way,
• 02:47 the difference between absolute and relative referencing, but
• 02:49 for right now, you know that when you copy your formula it will extend and
• 02:53 everything works nicely, which is great.
• 02:56 So we've been able to do multiplication across our table.
• 02:58 Wouldn't it be nice if we could also add up our quantities?
• 03:02 So let's try this.
• 03:03 We know that plus is used for addition.
• 03:05 We'll say, = 56 + 84 + 21
• 03:10 + 65 + 52, and hit Enter.
• 03:15 And it gives us 278.
• 03:17 And we can prove this out as well by selecting these cells and
• 03:22 looking in the quick access toolbar, where it says sum is 278.
• 03:28 But working with formulas in this way, while useful, can cause us some problems.
• 03:33 Here's why.
• 03:33 Let's right click and insert a new row.
• 03:37 And we'll put in a quantity here of 10.
• 03:39 And you'll notice that it still returns 278 because
• 03:44 the cell that we needed isn't pulled into the formula anymore.
• 03:49 So rather than do that, why don't we delete this and
• 03:52 come up with a different way.
• 03:53 The way we're gonna do this,
• 03:55 we're gonna select the cell immediately below our data.
• 03:58 We're gonna go to the AutoSum button on the Home tab, and
• 04:03 noticed that there's several different types that we can get.
• 04:05 Sums and Averages, Counts of Numbers.
• 04:08 I'm gonna go with the Sum because I want to add these up.
• 04:10 And you'll see that when I do that, its sums the entire range from B5 to B10.
• 04:17 So now, when I hit Enter, it gives me 288.
• 04:20 And if I select this, I can see that, indeed, it is 288.
• 04:24 And if I were to right click and delete this row.
• 04:29 It will recalculate to 278.
• 04:31 The reason being is I'm not picking up specific cells anymore,
• 04:35 I'm picking up a range.
• 04:36 So that works much better.
• 04:38 Let's try this again.
• 04:39 We'll go over to the Worth column and say AutoSum, give me the sum.
• 04:44 Gives me D5 to D9 and I can hit Enter and
• 04:48 it will automatically put the sum in there as well.
• 04:50 So this is really really useful.
• 04:52 We can write formulas that are picking off individual cells like we've done here, or
• 04:57 we can actually write formulas using some functions, which we'll explain in a lot
• 05:01 more detail later to pick up entire ranges of cells as well.
• 05:06 So I say Excel is the world's best calculator, and
• 05:10 this is part of the reason why.

Lesson notes are only available for subscribers.