The Complete Course from ExamCollection industry leading experts to help you prepare and provides the full 360 solution for self prep including MO-200: Microsoft Excel (Excel and Excel 2019) Certification Video Training Course, Practice Test Questions and Answers, Study Guide & Exam Dumps.

Level 1, Section 4 - Basic formulas

2. Order of operations

Now, it's very rare that you'll just have two numbers in a formula. You'll probably have more. So let's do another one. Another formula equals seven times eight plus six. Hang on, is it going to do seven times six, which is 56, and then add six to it, which is 62, or is it going to do eight plus six, which is 14 first, and then multiply by seven, which is 98? Well, let's see what it does. Right, 62. So it does seven times eight plus one, then first, and then does plus or minus six. So I'll just put this formula here as a description. There we go. So, what happens if we do it the other way around? So that equals six plus seven times eight. Will it now do the six plus seven first, which is 13, and multiply by 98, which is 104? What do you think it's going to do? Let's see if you're right. and the answer is 62. And again, this is the reason this is called the order of operations. And what this means is that, first of all, Excel reduces power. So if you give something to a computer's formula, it will start with powers. Now, another way of describing powers is exponential. So eight to the power of two is an exponential operation. Then it will do multiplication, then division, addition, and finally subtraction. But how are you going to remember all this? Oh, please excuse my dear Aunt Sally. So please excuse my dear Aunt Sally. You'll notice that the first letter of each of these represents a letter from Please excuse my day and Sally, exponential multiplication, division, addition, and subtraction. So what does the P stand for right at the beginning? Well, P stands for parentheses, also known as brackets. And there is an alternative way of remembering this, which has the B for brackets, and it's called odd mass. So we have division and multiplication and addition and subtraction. So it depends on how you've learned it. Please excuse my dad. Inform me or bod mass. So what are these brackets that we've just been talking about? Well, these allow you to tell the computer exactly what order you want a formula to be done in. So let's have a look at this formula. For instance, seven times eight plus six So what I could do is put brackets around the seven times eight. So I'm telling the computer to do seven times eight first and then add the six. So it's not changing the answer, but the actual expression may look a bit clearer now that you know it's going to do multiplication before addition. It hasn't fundamentally changed anything, but it has made something clear to ourselves. Now, what if I wanted to change that and say it equals seven times open bracket eight plus six? Will that change anything in the answer, or will it come back as 62 again? and the answer is yes. We are asking it to work out the expression that's in the brackets: first eight plus six, making 14, and then multiply 14 by seven, giving us the answer of 98. Now, we're not just limited to one set of brackets. We could say that equals seven times the number of brackets, four plus brackets, four plus brackets, six if you wanted to, a lot of unnecessary brackets, but it will allow us to do that. So it will take four plus six with the ten-added number 414 and multiply it by 798. Now, notice what happened when I was looking at and editing all of this. You can see that the brackets and parentheses have different colors. Some brackets are red and some are green, though that's a bit more difficult to see. The inmost brackets are purple. So what's happening? Well, the computer is colouring different inner brackets, so these two brackets become purple, and then the next two become red, so you can see more easily what's going on. So if I were to type in "equals one plus brackets," two plus brackets," three plus brackets," four," Notice what's happening down here in the results area. The brackets are being coloured different colors, and you can see when you're on the outermost one because the last bracket is a black color. It's just a helpful way the computer is helping me. All of these brackets, of course, are not actually needed because it's going to do all of the additions. It wouldn't make any difference in which order all of the additions are made, but it might be necessary if you're going to change one of these additions to a multiplication. So it could just be helpful for you to insert brackets or for the person that you're giving this spreadsheet to. To be 100% clear, what you've put in the formula is exactly what you mean. So please excuse my dear Aunt Sally.

3. How to enter numbers and dates in Excel

Now, a quick word about how to enter numbers and how to enter dates. Now, we saw earlier that eight is the power of a half. Now, it could be that you're using a locale that doesn't use the full stop as a decimal point. So for you, it might be "equal eight kara," not "comma five." and you can see which locale you're in. If you go to the Start menu and go to the Control Panel and I click on View by Lodge Icons, I can go down to the Region section and you can see that I'm set up for English in the United States. So my short date is "month followed by date, followed by year," for instance. And if I click on Additional Settings down here, you can see that the decimal symbol is a full stop. Now, I can change that if I so wish, but really the key point of this is how to enter data. It's entirely based on how your computer is set up. For example, we have the Digit Grouping System symbol being a comma over here in this American locale. So if I type in the number one, comma two, three, and four, that's perfectly acceptable. And the computer will interpret that on my system as 1234. You can see that it doesn't have the comma in the formula bar, but it has been retained in the formatting of the results. If I were in France and typed one comma, two, three, and four, it would assume that I meant one and decimal places. In France, a number just under one and a quarter means 1234, whereas one dot two, three, and four mean 1234. So this is an instant reason why I prefer to type numbers without the commas and without the thousand separators. So I could type 1234. I could type one comma, two, three, and four. The underlying number is still the same in my American system. Now, for dates, you have to enter them as per your locale. So suppose I want to the 1 February 2020. Well, in America, I'll type "two commas, zero, one, 2020." If I were in England, then I'd type one domain comment with slashes—one slash, two slashes. Alternatively, I could use hyphens: one hyphen February or Fed.Hyphen 1020. If I was in Japan, I'd be typing 2020. Basically, whatever way you do it is acceptable as long as it correlates with the region that your computer thinks you're in. And also, while I'm on the subject of putting things in formulas, you can also type in a percentage time. So you could say five equals 5% multiplied by 400. That's perfectly acceptable as well. So that will calculate at 0.05, multiply by 400, and that's 20. So the percentage sign is just shorthand for "divide by 100."

4. Referencing cell ranges in formulas

So far, so good. We've worked out how to use all of these basic operators, but this is technically anything that a calculator can do. So what's so special about Excel? Well, let's just rename this sheet so I can come back to it later and know that it's the operator sheet. And I'll open up a new sheet, and I'm going to call this cells. So that just happens to be the name of the spreadsheet. So I'm going to put in a number here. Now I want this number doubled. So a double number equals four times two, or eight. So far, so good. Except when I changed my original number in that cell from four to six, my answer doesn't change; it still equals four times two. And the problem lies in that I have hardcoded this four-digit number that's in the formula. Now, quite often I try to avoid hardcoding any figures simply because I want to change them later. And it might make it easier if I could actually see the figures on screen rather than having to hunt for them in formulas. So instead of referring to a particular number four, I want it to refer to this particular four that is in a different cell. So I can't just say, "Well, give me this cell right here." I've actually got to give it a name. And the name that Excel has come up with is give each column a letter ABCD" and give each row a number, 1234." And so what you have in a particular cell is a particular column letter and then a raw number. And it's always that way. Column number first, column letter first, and then row number. Now, if you're used to geometry, then you know it's the other way around. You put in the X axis first and then the Y axis. Whereas in Excel we do completely the way around. We put in the column first and then the row. So this cell is cell D three. And you can also see it's D-3 because it says so here in the name box. When I click on the cell, it says D three.So I want to change my formula to equal four times two. This four should be changed to a reference to D three. So what I'm going to do is just type in "D three." Now notice what happens instantly. The cell D three becomes blue. It's got blue cursor square around it. And also notice that in the formula box, formula barD 3 is now in blue as well. Those two relating to each other. So when I press Enter, will the three remain blue? No. But what's happened now is that the cell is accessing what's in cells D 2 to D 3, doubling it, and giving me that answer in cell D 4. Well, it looks the same. But now I can change what is in cell D three.So I'll change that to six, and I can move to a different cell, a completely different cell. As a result, the formula becomes twelve six times two. Now, I didn't have to do anything. I didn't have to click Refresh. I didn't have to go into the formula and press Enter again. It's all automatic. Now, if I want to change this formula again, I can click inside the formula bar and notice what happens. Nothing yet. But click inside the formula bar, and cell D3 becomes blue again. Now suppose I have another number over here. So I've just typed in the number ten into what cell? E three. Now suppose I don't want my formula anymore to referto D three, I want to refer to e three. Well, I could go into the formula and just changethe number, the reference from D three to e three. So now, you can see the blue has changed location. But there is another way. If I go into the formula bar, you can see this box around D 3. Well, I can drag the top part. Don't drag the actual squares, but drag the line to a different cell and notice what happens in the formula bar; the reference changes. So press Enter, and the blue disappears. But we have the new formula, "Equals e three times two." Let's change that back, click inside, and drag the cell reference. Technically, this means that D-3, the highlighted cell, is a predecessor, a precedent of D-4. In other words, D four relies on D three. Therefore d three is a precedent of D four. And D four is dependent on D three. So we have precedence and dependence. Now, I can say "add one to this number." So I want set D three, set D four, and I want to add one. So I'm going to type in the equal sign. And now I'm going to click on 04:00 a.m. I'm going to finish editing the formula. No, because as soon as I type in the equal sign, the computer switches to formula mode. And when I'm still editing a formula and click on another cell, it's going to bring back a reference to that cell. So I've just typed in equals. I've clicked on D four, and now theformula equal is now equals D four. So now I want to add on to it. Get the answer 13. So I've got a formula that is dependent on another formula, which is dependent on a value. We can make this more complicated by adding both numbers together. So I want to add D four and D five. So equals D four plus D five. Notice. Now D five is in red. And if I edit in the formula bar, you can seethat D five, the reference in the formula, is also red. So this is how the computer is attempting to assist you by indicating that you are saying "D-5." I'll show you where D-five is on the screen. Follow the same color. So with all of this, we can use the same operators that we had: subtraction, multiplication, division, and exponential. That's the power of adding brackets. So what I'd like you to do is a little exercise. So I'm going to add a new cell, a new sheet. I'm going to call it exercise. So I want you to type in the number three. So I'm just going to do it for you there. And then I want you using formulasin the same way that we've done. That refers to cells. Where possible, I want you to double the number. As a result, three times two equals six. I want you to add four. So that makes ten. Subtract two; that will make eight. And then in just one formula, add four, then multiply by six. And then subtract the first three digits that you see on the screen. So the answer you should be getting is 69. So we're getting three, then six, then ten, then eight, then eight. Add four to twelve. Multiplying by six is 72. And then subtract the first number to make 69. But I want you to do this using formulas that refer to other cells. So I'm going to save this right now. So save the file as "browse." going to add a new folder. Level one, session four. Level one, session four. And I'm going to add this as a resource to this video. So if you go into your videos, you can see the exercise that I've just paused in the video. If I click on the little icon in the top left, I will have a little reference like this music list that has this particular sheet, this particular workbook, and you can open it up and do this exercise. Now, if you've got any questions about this exercise, please let me know. Please send me a message and I'll get back to you. So if you want to send me a message, you can do so by clicking Browse Q&A and typing in your message there. And if you are so inclined, could you please review this course? So go back to the dashboard and put your review up here. Five stars, four and a half stars if you think it's great. Five stars. If you don't think it's great, let me know why. Because I would like the next course to be even better for you. So please download this workbook. Please try all of these formalities, and good luck. I'll see you in the next video.

5. Solution to the above exercise

6. Practice Activity Number 2 – Solution

So how did you do? So let's see how I handled this. First of all, create a new workbook using an invoice template. So I'm going to go to "File New" and I'm just going to look for the word "invoice"—it doesn't really matter—and press Enter. It doesn't really matter what particular invoice I'm going to use. I will click on that and create. Now attached to these instructions is aresource called the Music List text file. So I need to open that up in Excel. So, file open, and here's my previously downloaded MusicList text file. and it's asking what sort of file this is. Well, hopefully you can see from the preview that it is not fixed width because the second column is not lining up correctly. So it's delimited and it's tab delimited. So there we go. So click next. Click. Finish. Display both of these on the screen at the same time. There are lots of different ways you can do that. I've gone through a few ways. So here is one way. I'm just going to drag the sides so I can see both on the screen at the same time while seeing all of my instructions. Add a new spreadsheet to the musicalist workbook. So I'm just going to give it a bit more space. And I want a new spreadsheet, and I've got a new tab. So just click on this plus sign to move this newssheet tab to before the music list spreadsheet tab. So I'll just drag it. Alternatively, I could have gone to the right and clicked on move or copy, which would have moved it ahead of the Music list. In this blank spreadsheet, enter the following values in cells A one, B one, and C one. So you have to actually identify whatis aone b one and C one. So that's 410 and 30. I'm using Tab to go in between them, but you could use the arrow keys as well. In selling a two into a formula that returns the value, which is a one to the power of two, As a result, one Cadillac equals shift six two. In B two, enter a formula which returnsa value which is double the value ofcell B one equals B one times two. In cell C 2, enter a formula that subtracts two from the value in cell C 1 and then divides by two. The answer should be 14. So hopefully you worked out that what you have to do is use brackets because you're doing c minus two and then dividing by two. So equals open brackets c one minustwo, closed brackets divided by two. in cell d one.Enter the date the 1 January 2019as a date rather than text. So I didn't want you to type 1 January 2019 literally, but rather all one slash or one slash 2019, unless you're in Japan, in which case 2019 one one. Save the workbook in binary form as my second practise activity. So I'll save the file as I navigate to a suitable folder. So in level one, session four, I need to save it as a binary-form workbook XLSB, and I'm going to call it my second practise activity. And click Save, and you can see the name right at the top. So how did you do? This is the culmination of this level. So hopefully you are confident with the skills that you have learned so far, and if so, I'll see you in level two. If not, then the great thing about this video course is that you can go back to previous videos and go, so what did Philip actually mean in that bit? And perhaps a second opinion, yougo, oh, that's what he means. So that's the great thing about this video call service. So to go to a different video, just click on the little icon on the top left and click on whatever video you want to go to, and it plays. So either way, thank you very much for joining me, and I'll see you in the next video.