From the course: Excel Essential Training (Microsoft 365)

Using simple formulas

From the course: Excel Essential Training (Microsoft 365)

Start my 1-month free trial Buy for my team

Using simple formulas

“

- [Instructor] For many Excel users, writing formulas, creating formulas, using functions is one of the most common things they do as they work with Excel. In this worksheet, we've got some data set up and we like to enter the profits here, and the numbers are certainly easy enough. We could do most of these in our head, but we do need formulas here, and in real life scenarios sometimes these numbers are quite a bit larger. We also want to have the freedom to make changes and have our formulas adjust too. So in cell B four, we want to be subtracting these two and we actually will not be typing in the numbers. One, two, zero or 100, we use cell addresses, and that's quite common in Excel. In fact most formulas are not about using pure numbers, but referring to locations instead. Formulas begin with the equal sign and there are three ways we can enter this formula. Typing, we want to say sell B2 minus B3. Whatever's in B2 minus whatever's in B3 and there's our answer. Another way to do this doesn't involve typing as much, but it does involve the use of the arrow keys. We can start with equal. I'll press the up arrow twice, then we'll put in a minus. Now I'll press the up arrow once and enter. And a third way might involve two hands as we use both the keyboard and the mouse. Equal, click this cell, minus, click this cell, enter. Same answer in all cases here. We also want a formula over in cell H2 and if you hadn't seen many formulas, maybe just the one we did here, you might be saying, well I guess we've got to do what equal and then type or click on B2, then put in a plus and do the same thing with C2? You mean we got to do this four more times? And what if it were 12 months? Although we could get a correct answer, this is not the best way to handle adding up multiple cells. Built into Excel are over 500 functions and probably the most commonly used one for almost everybody is SUM, =sum, left parenthesis. And we're going to be highlighting these cells that we want to add up. We can either click and drag left to right, or right to left, makes no difference. So I'm pointing into cell G2, holding down the left mouse button, dragging leftward, and simply pressing enter. When you use more than one function together, that's called nesting. You do need to make sure all those parentheses are typed, but in this case, I'll simply press enter and we have an answer. We also want to do an average here. And the description of an average, I'm sure you all know it, is we add up a bunch of cells and then divide by how many cells we added, but we already did the adding right here. So we can simply type =H2 divided by six, and enter, and there's our answer. We will adjust that eventually, of course and in the next movie, we'll show you how to be copying these kinds of formulas into other sales. Now, over in cell A6 are some obvious symbols that everybody knows, minus and plus. The slash, and there are multiple ones on the keyboard, but the one that is angled this way is the symbol we use for division, and the asterisk is used for multiplication. A few of you may need to use this other symbol, the carrot symbol for raising numbers to powers, but from time to time you might need to use parentheses as well. So I'm going to write a simple formula here in cell C6. I want to know the percent of increase from January to February. Now, one way to do this is to subtract these two and then divide by the January entry, but if you write the formula the way I'm doing it now, equal C3 minus B3 and divide by B3. What you wouldn't know until you see the result is that the division takes place first. Actually, we're going to get C3 minus one, which is not the correct answer. So what do we need to do here? I'll double click back on this to edit. We'll simply put that C3 minus B3 within parentheses. So from time to time when you're working with formulas and you're not getting correct results, often what you'll look for is misplaced or missing parentheses. Now we will have a correct answer. It's not formatted. We take care of that a bit later, but it is correct now. So at certain times you will need to use parentheses. In the next movie, we'll show you how to copy the formulas we've already written into other cells.

Contents