Formula Anatomy

Quick reference

• 00:03 One of the most important features in Excel are formulas.
• 00:09 Well, what the heck is a formula anyway?
• 00:12 What a formula is?
• 00:13 Is, it's essentially Excels way of working through an equation to
• 00:18 come back with a result.
• 00:20 The nice thing about this is that we can feed it all kinds of different pieces,
• 00:23 some of them numbers, some of them text.
• 00:25 And Excel can react to it in different ways to give us different outcomes,
• 00:30 which is really, really neat.
• 00:32 One thing that we have to remember about formulas is that every single formula in
• 00:36 Excel, always starts with the equals character and
• 00:39 this is the way that we can tell whether or not something is a formula.
• 00:42 If it starts with equals, it's a formula.
• 00:45 If it doesn't, it's not.
• 00:46 It's just that simple.
• 00:48 One of the interesting pieces though is that we can actually build formulas either
• 00:52 by starting with the equals key or with a couple of special characters, as well.
• 00:57 One of them is the minus key.
• 00:59 But the minus key, once you type in minus 5 and
• 01:02 Enter will actually convert in the Formula bar to equals minus 5.
• 01:06 So, it always puts that equals sign in front.
• 01:09 The same is true if you start a formula using the plus sign.
• 01:13 Plus 5, plus 2 would return equals 5, plus 2 in your Formula bar later.
• 01:19 Now formulas as I mentioned can be used to do all kinds of cool things, we can start
• 01:24 with simple things, like adding two numbers together equals 1 plus 5.
• 01:29 The only difference here between what you would type into your calculator and
• 01:33 what you want to say to excel is you start in the cell and say equals and
• 01:37 then the equation that you want.
• 01:39 Where normally when we work with a calculator, we're more likely to say,
• 01:42 1 plus 5 equals.
• 01:43 So it works a little bit backwards, but the reason is because Excel already has
• 01:48 the cell to hold the answer and it wants to know what that cell should be equal to.
• 01:53 So that's how it sorta works through it's stuff.
• 01:55 The other thing that we should remember with this is that - and
• 01:58 this is where things start to get really powerful - is that we
• 02:01 can actually use ranges to actually feed our formulas.
• 02:04 So if A1 contains the value of one and B1 contains the value of 5,
• 02:10 then this particular formula equals A1 plus B1 will evaluate to 1 plus 5 and
• 02:16 would give us the answer of 6.
• 02:18 We can also perform more complex operations and
• 02:21 this is also very good, because Excel actually follows the order of
• 02:25 operations whether you refer to it as BOMDUS or PEMDUS or
• 02:29 anything else, it works to it's brackets first and then it's exponents.
• 02:34 And then it's multiplication, division, eventually addition and subtraction.
• 02:38 And we'll look at that in more advanced videos.
• 02:39 But for right now, why don't we go and
• 02:42 look at the very basics inside Excel as to how formulas actually work.
• 02:48 All right.
• 02:49 So here we are in Excel now, let's practice building some formulas.
• 02:54 What we're going to do is we're going to take a look at the mathematical equations
• 02:58 down in column A here and we're gonna build those using formulas.
• 03:01 Remember, the key is always to start a formula with equals,
• 03:03 must start with equals.
• 03:05 So that's exactly what we're going to do.
• 03:06 We're going to say, equals 1 plus 5.
• 03:10 And when we hit Enter, you'll see that it equates to 6,
• 03:14 which is exactly what we'd expect to have happen.
• 03:16 We can do the same thing with the next line, as well.
• 03:19 Equals 8 minus 6 and that comes to 2.
• 03:24 The nice thing about excel formulas is that they do follow the order of
• 03:28 operations whether you know it as BEDMAS or BOMDUS or
• 03:30 PEMDUS however it goes, it always works with the brackets then exponents and
• 03:35 then multiplication division and addition subtraction in that kind of in order.
• 03:39 So if we look at this particular formula, we would expect that when it's actually
• 03:43 put into Excel and calculates, it will do the stuff inside the brackets first,
• 03:47 1 plus 5 equals 6 and then we'll do the multiplication of 2 times 2.
• 03:50 So, we should end up with 6 minus 4 equals 2.
• 03:53 So let's check it out, equals open brackets,
• 03:58 1 plus five and we close our brackets minus 2.
• 04:02 We're gonna use the asterisks for multiplication instead of the x, times 2.
• 04:07 And when we hit Enter, indeed it comes back and
• 04:09 works out to 2 exactly as we thought it would.
• 04:12 We could also type without typing equals, first.
• 04:17 Type in 8 divided by, that's the slash 2.
• 04:21 And you'll notice that when we go back, we didn't type in equals on this, but
• 04:25 when we look in the formula bar, we can see that it has prepended one for us and
• 04:30 that's important.
• 04:31 Remember, every formula starts with equals.
• 04:33 Whether you start it with minus, plus or just by putting in the number, it is going
• 04:38 to put an equals in front of it to turn it into a formula and that's important.
• 04:42 Now, we can also do the same kind of thing, but using ranges.
• 04:47 So rather than typing in equals 1 plus 5, we have 1 and 5 over here.
• 04:52 Let's say, equals 1, so C10 plus the value in D10, which is 5.
• 04:59 And when we hit Enter now, it again comes to 6.
• 05:02 The beauty of this though is when I change cell C10 to 2 and hit Enter.
• 05:08 My formula now recalculates, which is great.
• 05:12 Now, I'm just gonna undo that and
• 05:13 we'll go down and we'll just quickly plunk in the rest of these guys.
• 05:17 Equals C11 minus D11, returns 2
• 05:22 as we would expect, equals open brackets.
• 05:29 Take this cell plus the cell next to it,
• 05:33 close our brackets minus 2 times 2 and hit Enter.
• 05:40 And again, and it calculates to 2.
• 05:42 And once again, we can type in minus 8 divided by 2 and hit Enter and
• 05:47 you'll see that our formula, again converts puts equals in front,
• 05:53 but gives us the appropriate cell references.

Lesson notes are only available for subscribers.