Basic Math

Quick reference

• 00:04 One of the reasons we use Excel so extensively in the real world is because
• 00:08 it's one of the world's greatest analytical tools, and
• 00:11 one of the world's greatest calculators.
• 00:14 We can do all kinds of mathematical calculations and
• 00:17 build business intelligence solutions in Excel
• 00:19 as long as we know how to actually write formulas to make that happen.
• 00:23 Now, the first thing that we need to be aware of is,
• 00:25 what symbols does Excel use for calculating mathematics?
• 00:30 And, as you'd expect, they're gonna look a lot like this,
• 00:35 addition, we use the + sign, subtraction the- sign, multiplication,
• 00:40 we use the * instead of the x, division is a /.
• 00:45 And for exponents, we use this little hat icon which is
• 00:49 Shift+6 on the North American keyboard and I believe is similar around the world.
• 00:54 Now if we want to actually write a formula, how would we go about doing that?
• 00:59 Well let's try and
• 01:00 work this out right now by looking at the cost of our soccer balls.
• 01:03 We wanna try and figure out what the total cost is.
• 01:06 So in order to set this cell equal to something,
• 01:10 we would type in = and now we can say 56 * 8.99.
• 01:15 And then we can hit Enter, and Excel will do the calculation for us.
• 01:20 And if you grab a calculator,
• 01:21 you can prove it out that this is exactly mathematically correct.
• 01:25 But there's a challenge, because what happens when the cost or
• 01:28 the quantity of our soccer balls goes to 57?
• 01:31 Well, it's still pointing to the 503.44 because we've multiplied 56 times 8.99.
• 01:38 So I'm gonna press Ctrl+Z to undo this for a second.
• 01:41 We'll set it back.
• 01:42 And I'm gonna show you how we would actually do this in Excel.
• 01:45 We would say, equals and
• 01:49 we gonna click on cell B9 times cell C9.
• 01:54 And now we can hit Enter and you'll notice that we still get the same calculation.
• 01:59 Why is this better?
• 02:00 This is better because we can now Ctrl+C to copy this and
• 02:05 we can bring this all the way down and Ctrl+V to paste it.
• 02:09 And you'll notice that when I go and click on the 669.48, if I go and
• 02:14 click in the formula bar, it's now multiplying these guys.
• 02:17 And we'll talk a lot more about how these formulas actually extend as we go through
• 02:21 different modules.
• 02:22 But the key thing to recognize is that we've multiplied B13 times C13, so
• 02:26 we've got the correct price.
• 02:29 And now if the cell changes to 57, and
• 02:33 we hit Enter, it automatically recalculates to show us the correct value.
• 02:38 I'm gonna press Ctrl+Z to undo that again, but you can see how
• 02:41 working with ranges in our formulas actually allows us to be quite robust.
• 02:45 And there's no need to reach for a calculator on the corner of your desk and
• 02:48 put in a hard coded number because this automatically recalculates for you,
• 02:51 which is great.
• 02:53 Now, what if I wanted to go and sum up all of these values?
• 02:57 Well, I could do this.
• 02:58 I could say =, and we could go and grab the quantity, + the next quantity,
• 03:03 + the next quantity, plus the next one, plus the next one, and we could hit Enter.
• 03:09 And this would give us 278.
• 03:11 And we can check that either by adding on a calculator or
• 03:14 by selecting the cells here and seeing the sum in our quick information is 278.
• 03:19 But this has a challenge, while it works, it works now.
• 03:24 What happens if I insert a new row and put 10 units here?
• 03:30 Notice it didn't change.
• 03:32 If I grab all this data, the total should be 288, and
• 03:35 the reason being is because the formula that we wrote misses that cell.
• 03:43 So is there a better way, and indeed there is.
• 03:46 I'm just going to delete this cell.
• 03:48 We want to use a sum function.
• 03:50 Which you can actually get from the Autosum button.
• 03:55 And if you look at the options for Autosum, we have the ability to sum or
• 03:59 average, count number, max, min or even more functions.
• 04:03 Because I'm immediately underneath a contiguous column of values here,
• 04:07 I'm gonna press Sum and you'll notice that it writes the formula I need for
• 04:12 me, equals sum B9 to B14, and this is actually an entire range.
• 04:17 And now, when I hit enter, it gives me 288.
• 04:20 And what's interesting about this, is that I can right click and delete this row, and
• 04:25 it'll go to 278.
• 04:26 Or I could even right click insert a new row and
• 04:29 come back and put 20 units here, and it'll go to 298.
• 04:34 Now, I'm gonna get rid of that one, cuz you get the idea.
• 04:37 And I'll show you that we can also do the same thing over here.
• 04:41 I could grab all this data I can go to the Autosum button and say Sum and
• 04:45 it will immediately say, well, everything you selected, the last cell was empty.
• 04:51 So I'm gonna put the sum right in there and
• 04:53 it's gonna sum up this range of data for you.
• 04:56 This is where the real magic comes into Excel, this is why it's the world's best
• 05:00 calculator because you can type values into your ranges and
• 05:03 Excel can automatically run calculations and update them for you.

Lesson notes are only available for subscribers.