An Introduction To Using Formulas In Mac Numbers

hi this is gary with macmost.com here's a basic introduction to using formulas in numbers [Music] macmost is brought to you thanks to a great group of more than a thousand supporters go to macmost.com patreon there you could read more about the patreon campaign join us and get exclusive content and course discounts spreadsheets are a great tool that i think everybody that has a computer should learn how to use on a mac you can get the numbers spreadsheet app for free from apple in numbers here i'm going to go to file new and choose the basic blank template here and create a new document from it you can enter data into each one of these cells usually that means numbers let's as an example use two numbers here i'm going to type a 6 in this cell then click on this cell and then type a 7. i'm going to press return for it to accept that value so now i've got two values one in each cell now let's suppose i want to add these numbers together i can create a formula in this cell that adds this number plus this number the way to enter a formula is to go to insert formula and then new formula and now you're typing a new formula but nobody does that instead you use the keyboard shortcut equals just press the equals key on your keyboard and you go into formula entry mode now you can type a formula now notice that there are columns and rows columns have letters associated with them and rows have numbers so when you want to identify a cell you would identify it by its column and then into row so this is cell b2 this is cell c2 so if you want to create a formula that adds the two of them together you need to use those references i'm going to press equals here and then i'm going to type b2 and then plus c2 and you can see as i do that it even highlights the cells that i'm referring to now i can press return or click the green check mark here and you can see it gives me the total there so i have a formula that works now i do want to point out that if you were to just type a formula without using the equals key first you wouldn't get a result in fact you wouldn't be typing the formula at all you'd be typing what numbers thinks is label so if i were to type b2 plus c2 i just get that text in there you have to go and enter formula mode with the equals key and type it as a formula in this box in order for a calculation to be performed the great thing about formulas is they update automatically if you change the values so if i go into this cell and i press 9 to change the value to 9 and return notice how the formula automatically recalculates and comes up with a total of 15. also notice when i select a cell with a value in it like b2 if i look at the bottom left-hand corner it says actual meaning actual value and i chose 6. c2 shows actual value of 7 but d2 the cell with my formula in it will show formula and will show b2 plus c2 so the cell here shows you the result of the formula but to see what's actually in the cell you would look to the bottom left hand corner with the cell selected so you can see it's not actually the number 13 typed in there it's a formula and the results of the formula are what are being shown now i'm going to delete this by just selecting it and pressing the delete key and i'm going to show you another way to type a formula you can use equals to start and instead of typing b2 you can actually click on cell b2 and then you could type plus and then click on cell c2 if you want to move this out of the way you can grab it right here and drag it wherever you want if you like having it at the top here you can drag it there if you like and now i'll click on c2 and complete the formula and press the check to edit a formula all i need to do is click to select the cell then click again to edit it and the formula entry box will come up again and now i can either type or click or do anything i want to edit this so i'm going to select the plus here and instead change that to minus and now you can see i can do subtraction as well as addition if i want to do division i would do a slash that's the key on your keyboard that also has the question mark on it but it appears here as a division symbol and now it's dividing if i wanted to multiply you would use the asterisk on american keyboards that's shift and 8. you can see that shows up here as a little multiplication symbol and now it's multiplying the two now let's turn this into more of a proper table here instead of just numbers sitting out by themselves let's label the columns and rows you can see here the first row looks different than the others it's a header row and the first column also looks different that's a header column and these are used for labels rather than values if you go in the sidebar to format table you can see headers and footers and you could see i've got one header column and one header row so let's use this to label these i'm going to say that the first one is name second is amount and third is price the idea is these would be sales so the total would be the amount times the price let's put a name here and now that we've got these labeled the formula is going to look a little different i'll click on it once to select it and again to edit it and you can see instead of saying b2 it actually uses the labels so it says amount mark times price mark now you can still type it the same way so i could do equals and then b2 plus and you can see how it changes to amount mark c2 and you can see it changes to price mark i could also do the same thing by clicking so i can do equals click here multiply here and you can see it's using those labels instead now let's add another row to this so you can see here the second sale has a different amount a different price i want to do the same calculation here now you don't have to type it i could if i wanted to put equals and then this times this and get the right value but instead what i'm going to do is copy and paste i'm going to select this cell and use command c to copy then i'm going to go here and paste now it seems like this shouldn't work after all what i've copied here is b2 plus c2 and if i paste it here it's still going to be b2 plus c2 it's going to give me the total of 42 not the total of 40. but numbers and all spreadsheets are smart enough to know that when you copy and paste a formula it should adjust the rows and columns by the difference of where you're copying it from where you're pasting it to so since i'm copying from d2 and pasting in d3 it's plus one rows so it's going to adjust all of the references in the formula by one row so i'm going to paste in here and you can see it's giving me 40 the proper answer and you can see here the formula is amount jane times price chain and so it did in fact ingest this this is actually b3 times c3 it recognized it went down one row so it should adjust the formula by one row as well now let's enter some more data so you can see here i've added three more rows if i want the formula to fill out and complete these three rows i can just copy one of them and then select all these cells i can do that by selecting one and then shift clicking and selecting the last one to select a range i can also grab this dot here and stretch the selections now i've selected three cells and when i use command v to paste it'll paste that formula in all three cells and adjust each one so you can see here this is amount john times price john this is amount susan times price susan and amount tom times price tom and things will adjust properly even if you insert a row or column so if i go over here and click add row below it's going to insert a new row between 4 and 5. but you could see that this formula in this formula remained in sync with which row it was on it also knew enough to say well you had formulas in all of these so you probably want the same one here and you can see this is indeed b5 times c5 since we haven't entered a name here it's just going to use the row and column letters and numbers so let's enter a name here as soon as i enter this value you can see now the calculation is able to come up with the right answer now you can do more with formulas than just basic math like addition subtraction multiplication and division you can use what are called functions so for instance let's say i wanted to get the square root of a number i'll use this cell here as an example i can do equals and if you look here on the right you should see a functions browser and you have all these categories of functions and a list of all the functions right here you can scroll through them to see what's available you can also search so let's say i want to do the square root so let me search for root here and i can see a bunch of stuff there right here a square root whenever you find a function always look down below and read the description there's a description there are examples and everything this is the key to using any function in numbers always read through this and look at the examples so let's use square root i can double click here or i can just type sqrt but if i double click here it'll actually fill it out and show what it needs in terms of parameters values that it needs to process to get the result so in this case i've got num here i could type a number like 9 and then it will give me the result but i could also replace this with a reference to a cell like this one and now it's going to give me the square root of whatever is here so that's the basics of how functions work now let's do something that's actually useful for this let's say we want to get the sum of all of the totals here so to do that there is a function called sum i'll start a formula i'll look here and search for sum and it will come up and then i'll read carefully the description and examples then i'll use sum and instead of a single value you are giving it a range so i can click and drag to select this range notice how it's defining the range with two cells d2 and a colon d7 the range from d2 to d7 and then i'll close the parentheses there so this should give me the sum in fact it does and notice how all the formulas are going to update whenever i change something so if i go here and say well the price is going to change to this notice how the 40 to the right changes to 48 and then the sum will update now when using tables and numbers you should try to keep each row representing the same kind of thing in this case a record of a sale so row two row three row four they're all records of sales the headers are labels so these are labels here and the column here that's a header column there should be an identifier as well so in this case it's a name in other cases it may be dates or maybe receipt numbers or things like that but by looking here this identifies what the sale is and then here are all the details about it now we've got a bunch of rows here that don't follow this pattern first we've got a bunch of blank rows here these don't represent anything and then we have this row here which doesn't actually represent a sale but actually shows the total so the proper way to put together a table is not to have any rows that aren't the same kind of thing so let's get rid of the sum here so this is blank and let's get rid of all of the blank rows i'm going to just drag the bottom up by dragging this little button right here and all the way to the top so now all the rows do indeed represent a sale and the header row gives us the labels for it now the great thing about this is if you want to add another sale all you need to do is go to any cell in the last row press return and it instantly adds another row so we can now add something here and put some data in and notice how it automatically filled in the formula there for us when we added the new row so all we need to do is enter the raw data and now the row is complete we can also get rid of the columns here we don't need the extra columns but how do we get the total well the header row represents labels but you can also have a footer row that represents calculations things like sums or averages so let's add an extra row here at the bottom by dragging this down one and then in format table i'm going to add a footer row right here one footer row and now you can see this is a little bit different it's a slightly different style than the rest and there's a thicker line above it now in this cell here let's delete what was left there and instead create our sum now we could drag and select that whole range and then we get the sum but since we're using a footer row we can do something special if you do sum and then click here just to get the name of the column you could just use that and what that represents is every regular row in there so everything but the header and the footer you could also have just typed the letter d and it would do the same thing and now we get the total now anything we add here will just push the footer row down and the total the sum will take into account all of these rows so those are the very basics for using formulas and functions to do calculations in a spreadsheet i've got many more videos on using numbers here at macmost.com and also a course if you're interested in that i hope you found this useful thanks for watching [Music] if you like this video click the thumbs up button below to let me know i publish new tutorials each weekday hit the subscribe button so you don't miss out

Channel: macmostvideo

Views: 207,038

Keywords: numbers for beginners, mac numbers intro, intro to mac numbers, introduction to mac numbers, introduction to numbers, how to use formulas in numbers, how to use functions in numbers, how to do calculations in numbers, numbers formulas and functions user guide, numbers formulas mac

Id: w30Br4XvLgM

Length: 14min 49sec (889 seconds)

Published: Mon Feb 14 2022