How to use Formulas and Functions in Google Sheets

hi there jamie keith here today at teachers  tech hope you're having a great day today   today i want to show you how to  create formulas and functions   inside google sheets so if you're brand  new to google sheets and wanting to learn   how these basic things work or maybe you  just need that quick refresher because you   haven't used it well so let's get started with  formulas and functions today at teachers tech just before we get started here just so you  know if you take a look down in the description   everything is timestamped so you can jump to  a certain part of this video to find something   specifically you're looking for and also i'm  going to share this spreadsheet with you so   you can just click on the link down below in  the description and it won't open up but make   sure you go to file and make a copy because it's  only in view right now for you to access it let's   get started right away here so if you're using the  spreadsheet notice all the tabs at the very bottom

with all the different things that i'm covering in  this tutorial today and you can just click on them   as you go through and practice on your own but  let's start with basic math uh formulas here so   if we're just putting in any formula what we need  to do first is put the equal sign in so i'm going   to and go ahead and just  write a formula so now i put the equal sign in   i could go let's go eight and we can put an  operation in we'll go addition and then we'll   put another number and just hit return so we  have 13 it shows our answer if i click in it   once if you see the formula bar right up here  you can see what the operator what the formula is   and i can change it up here too so i could  change my answer here it will adjust if   i double click in here it will also change so  this again i can change add and go through like   this remember you need the equal sign if you  just go ahead and put an operation in like so   it's not going to recognize it as a formula so you  do need the equal sign first now i would suggest   that you do your formulas like this i would use  cell reference what i'll show you uh but a couple   of reasons would be if i'm just looking at this  number and it's not clicked on i don't know what's   creating that number i don't know even if  there's a formula there if i click on it yes   i can get the quick look up in the formula  formula bar uh to know that but what i would do   is use a cell reference so like this so i want my  answer over here so what i'm going to do is put   my equal sign in first to start it and i'm just  going to tell this cell here and put my addition   sign in to add to this cell like this and so it  goes through and does the addition if i click on   it now and check my formula bar you can see it's  b8 plus b9 and i can see the numbers here but   you can quickly change these numbers so if i was  changing 2 to 3 notice as soon as i change that   there it turns to eight here so if i was going  down and looking at the different ones put your   equal sign first click on your cell uh use your  subtraction click on the next one and so on uh   remember there's multiplication and division just  take a look at all the different symbols there for   each one i'm not going to go through each of them  i'll just do one more as an example as uh at the   power so if i put that one in click the first one  so above my six with holding the shift and then go   through and then i get five to the power of three  if i change any of these i make it larger you can   see how everything changes instantly so that's  using cell reference just your basic formulas   in math inside google sheets all right so i moved  over to my functions tab here and if you've never

used functions they can speed up a lot of  things inside google sheets for you they   can calculate mathematical operations they can  look up uh data for you inside your spreadsheet   they could calculate date and time so a number  of different things and i'll be showing you   that through these to this tutorial here today  but let's start with some basic functions so   this is just going to be starting with the sum  function i'll show you how you can add them now   first of all let's just go back a step thinking  how i showed you last time to add up so if i was   using the equals here i could click on each one  as i go through just like i showed you before   in the previous part when i get to the end i could  hit my enter and i get to 170 it adds everything   up now you can see it up in the formula bar right  there now there's a faster way and this is where   functions come in to speed this up so the sum  function and just like uh just like what i   showed you before you need to start with an equal  when using your function so we hit there equal   we can start spelling some just like this and  it comes up so this it's already suggesting   what i might be wanting here it says d3 to d6  so i'm just going to actually first of all i'm   just going to go to the normal one without the  suggestion and then i could select the area that   i want and it is going to be the same thing if  i would have selected the suggestion suggestion   then it would have just saved that step but i'm  going to hit my enter and you can see it adds   it up very quickly a lot faster than going through  each step especially if you had a large row of uh   information you wouldn't have to go through and  hit the addition or whatever operation you're   doing each time so functions save you time now  i'm just going to move over to this one here so   i'm going to hit the equal sign again a different  way you can get to this you can see the suggestion   is there if i was clicking on it and that goes  a lot quicker i'm just going to go ctrl z undo   undoes the last step or command z on a mac so if  i go ahead this time i'm going to go to insert   up top here so if i go insert you can see  functions are right here up at the top are kind   of your most common one so we could go some like  so and i could go ahead and highlight what i want   in there and hit return now i could go through if  you just wanted to type it out if you look in the   formula bar up here you could just type out equals  sum and put your range in so it's not saying a   range when you're using that colon in between g3  and g6 now another thing i want to show you here   and i'll just quickly put the sum in here again  so as soon as i start typing i'm going to use   the suggestion here so i'll just uh and hit return  it adds it all up what i want to point out here   is if you take a look if i go ahead and kind of  highlight this area right here of what i just   summed up and i'm just going to move myself out of  the way and just follow my mouse down notice that   in the bottom right hand corner we have  this right here you want to take a quick   look of what might be happening highlight an  area and it gives you some average min max count   really quickly just for that quick visual if you  go and highlight if you go and highlight a bunch   of data like that and then you can go and take  that quick look at it so if i click off notice

it goes away so select your information that you  want and then it's going to appear again all right   so let's try a different one we're just going to  use count and remember you could go to the insert   or you could just start typing it put  your equals and if we start typing count   we can see it's right here so what do i want these  ones the count is just going to tell me how many   numbers so it's going to count it up it  should be 5 you can see it is 5 just like that   all right so something else i just want to show  you that you can do i'm just going to delete this   right here i'm going to go ahead and put my equals  and i'm going to sum this up here so i'll just use   my sum and i'm going to highlight what i want you  can actually add more numbers to this if you want   so if i used a comma and i could put any number  i'll just put 100 hit return so it added this   up it was 80 and then it added 100 to it so if i  click on it if you look in the formula bar you can   see it's adding 100 to it now i wouldn't i suggest  doing it that way so what i would do let's go move   over here is if we were summing something up we  could hit equals and we could go ahead do our sum   notice it's not right above but i can still grab  this time and this time i'm going to go   ahead and put a comma click on this spot here and  close it or hit return and it does the same thing   here so again using that cell reference versus  just type it in typing it in because you can't   see it unless you type in it to see the formula  say cell or double clicking to see it that way   too so i would suggest using that cell reference  so that's just your basics getting started with   functions let me show you some more now all right  other very popular functions

to use inside google sheets and this would be  average median and mode so if i was talking about   average of these four numbers right here i'm going  to type this out actually so i'm going to use my   equal sign and i'm going to type average like  so this is where i would start with my bracket   and then i i'm going to start identifying the top  of the range which would be d3 and as soon as i   type that notice that this is highlighted right  here i put my colon in and now i'm going to put   d6 so d6 will close it so i'm going to put  d and six and i can go ahead and hit enter   i get 42. 5 it averaged this you can see if i look  in the formula bar right up top here now last   time i showed you the insert uh up here where  you can go to your functions here's a shortcut   right here you can go get to these functions  very easy i'm going to go ahead hit average   here highlight what i want just like that now a  couple other as i said popular ones we can use   for functions if you're looking to quickly use  them if we could put equals i can start typing   median and you can see it suggested it i'm going  to go ahead it gives me my correct range you can   see by the highlighted that is the range that  i want to hit return and i get my median there   and i can do mode again start typing mode and it  you notice it's picking up because of this here   i'm going to go and click my range and  there we have it so very simple to have   to use functions like these ones listed here so  remember to be using the worksheet provided so

you have all this data here that you don't have  to go and enter your own and i'm just going to   show you quickly min and mac so if i wanted the  minimum number out of this list right here   if i go ahead and start typing in you can  see again the suggested comes up i could go   and put my range in like so hit enter and it's in  i could also go uh so click where i want my answer   where do i want here's my max so this is going to  give me the maximum number so this should output   50 because 50 is the larger number there and you  can imagine if these numbers these lists were a   lot longer i'm just showing you on these short  ones to show you how these work but you can see   how these functions can really add uh help you a  lot speed things up all right so now let's talk

a little bit about formulas and functions dealing  with date and time so i could go to any cell and   i could put let's say today's date here i'll just  type may 7th and it will enter in like so but   that is to that particular date so tomorrow if  i open this spreadsheet it's going to still say   may 7th on here let's say if i wanted it to change  all the time it's better to put a function in for   that and so what we do again we start with  our equal sign so if i go put our equal sign   and i just start typing today like this i'm just  going to uh open and close my brackets like so hit   return it looks the same as what i had before but  the difference will be when i open the spreadsheet   tomorrow it's going to be a day ahead so it would  say it would reflect tomorrow's date be may 8th so   it will change now i'm going to show you how  we can add or subtract with some of these dates as   well so let's say hypothetically my birthday was  on june 8th here and i want to see how many days   until my birthday so i could do a uh just a simple  formula with this so if i go equals i pick the   further out date here and i'm going to subtract so  i'll just go ahead and hit subtract here from this   one and i hit enter you can see it's in 32 days  so tomorrow when i open this spreadsheet or this   uh this tab if i was looking at this it would say  31 date 31 this one would stay the same because i   wrote that date in but this would reflect one more  day now let's try a different little example with   adding and what i'm going to do is let's say i'm  going to just hit equals i'm going to copy this   cell right here so i'm going to put this cell in  right here and what i'm going to do is i'm just   going to hit an addition here and say whatever  number i put in this cell is going to get added   to it so i'm just going to hit return on it and so  uh what i'm gonna do now is you can see it's just   saying may uh today's date but if i say oh maybe  the grace period is in 20 days and i go ahead   and enter 20 it just moved ahead now 20 days you  can see now it's may 27th so that's just some   basic with some functions with date there we  can also do this with time so if i go ahead   uh if you're looking if you want the time that's  happening right now that's what you're that's   what your function is so if i go ahead and put  equals and start typing now and do the same thing   you can see it enters in that time so these  are just some beginner tips with working with   date and time inside google sheets you can use  formulas to combine two cells from two different

columns so let me show you how this works i have  nancy smith in two different columns you can see   separated i want to put them into one so  i'm just going to go over here to start with   so we start with our equal sign again like so we  click on what we want so we want c3 if we use the   end symbol like this above your number 7 and then  i'm just going to click on the other one like so   and you can see it gives me the hint what's going  to happen here i can go and it gets nancy smith   and i can copy this all the way down if i want  like so by grabbing but that doesn't look quite   the way i want it i don't want it all in one i  want to be able to have a space between it so what   i'm going to do is i'm going to go to this column  here and i'm going to put equals so again i'm   going to start by with my bracket and then nancy  is going to be the first one and i am going to   put my n symbol again like so i'm going to  use my quotation mark here and i'm going to   give it a comma but i'm also going to put a space  in it because i want a comma and then the space   and then i'm going to close with another quotation  mark like this and i'm going to put another   uh n symbol and click on nancy smith like so  and close it and hit return and then you can   see it's written the way i want now i could copy  this down a lot of times you'll get the suggested   fill you can just click on it and it will go all  the way down so use formulas to combine different   columns if you have it separated first you can use  this formula to quickly go down and combine from   multiple columns all right so let's talk about the  if function here and when you're using if function

what you're trying to do is make a logical  comparison between conditions and let me give you   an example of this simple uh one here so what i  want to have happen if this spot says apple i want   it to say true but if it doesn't i want it to  say false and you're going to see in this case   so there's a spot say apple it should come back  true so let's create an if now we start with our   equal sign or if statement we put our equal sign  and i start typing if and you can see it comes   up i'm just going to click on it i just want to  point out if i'm going to close this right here   if you don't see it just this little question mark  it opens it back up because this helps you walk   through when you're especially when you're first  learning to walk through your different steps so   if what do i want to have happen so if i'm going  to click on this spot c6 equals so i'll put the   equals and i'm going to use my quotation mark  if it equals apple and i'm spelling exactly how   i want it so that's my condition here i want it  to return something so i'm going to say return   true here so i spell that correctly and  i'm going to so notice it's at this part   right now and it says put a comma i'm going  to put a comma if not it is going to say   false and i'm just going to end this up and  hit enter so it came back true i'm going to   copy this down to the next spot so it's going  to look at this spot and says it's going to say   hey if this is apple return true now if it  doesn't return false this is orange so it should   return fault so let's do a little copy here we'll  just copy it down and we get our false one here   let me give you another example here so i have  a number in here and what i want to have happen   is uh whatever number this is if it's less than  100 it's going to say less than 100 here if it's   greater than 100 it will say greater than  100 so let's go ahead and hit our equal and   our if and right away it suggested the one  up above we don't want that we don't want to   suggest the example i just did i'm going to go  to a new one here so if i'm going to say if 50   is less than 100 so that's my condition there i'm  going to go ahead and put a comma i want it to   say something so what i'm going to do is start my  quotation mark i'm going to say less than 100 and   i'm gonna end it because this these are words that  i'm going to have come up and now i'm going to put   my comma in and notice it goes to now what's my  false value going to be so my false value is going   to be and i'm going to write this out so i start  with a quotation mark is greater than 100 and i'm   going to close it with another quotation mark and  and bracket and enter so it looked at this and   said is this less than 100 and it said yes it gave  me back my first condition that is less than 100.    if this number changed to 120 notice that this  changed to greater than 100. so that is an example   some easy examples of using uh if statement in the  function and how it can work for you okay so now i

want to show you a very powerful popular function  inside google sheets and that's the lookup and let   me give you an example what it does so i just  have these the simple data here some apples and   different fruit and some amounts if i type in  something here so if i was going to type in apples   and hit return it gives me back the corresponding  amount so it can search through this column right   here and then give me the corresponding amount  back so if i was looking for lemons here if i type   lemons i'll just click on it brings me back  40. so what i'm going to show you now is how to   set up this vlookup using this column here and  what we start with again we start with our equal   sign like this and i'm going to start typing vl  and you can see it's right there so i'm going to   follow along here and to make sure i get all the  parts correct so vl what am i searching so what am   i searching anything that's in this cell right  here so whatever is going to be selected so i   go in there and i'm gonna put my comma so  now it went from this next step what's the range   and so the range is everything in this table right  here f8 to g11 and i'm going to hit comma what is   the index so what is meant by that so there's  these different columns so the index of this   column would be 1 and this one would be 2. so this  is where i want it to come out of so i would put   a 2 here and i'm going to put a comma again  here so is sorted so if you want the exact   match to come out by default it's going  to be true but if you want the exact match   it's going to be false so if i am going to type  i'll type false here and then just close it   so you can see pork and it returned 10. if i  go ahead and type fish it changed it to 50. and   i'm going to just change this real quickly i'm  going to go over to this one here and i'm just   going to modify click in here and i'm going to  change this one to true and watch what happens   make sure and i'll put true in here so  you think well that's working all right   but look what happens if i type oranges here it  didn't go through and change it that based on so   it's not finding that exact match on some of these  here so if i go back and change it back to uh   false on this one and you'll see it start to work  again so making sure if you have that exact match   with it making sure you pick faults i just wanted  to point out too so with any of these here so if   i go in if you go to the bottom and i'm going to  hit learn more they give you great examples here   right i'll move myself out of the way and you  can kind of go through and gives you definition   of each spot here so you can see how they talk  about how false is recommended on these ones so   it will give you extra notes if there's something  you want to look up make sure you go to that learn   more and use the help that's built right into  it but anyway so that's vlookup a very powerful   powerful function inside google sheets  so conditional functions allow us to sum

average min max count based on the criteria  criteria or condition that we choose now let me   give you an example here in this case i have  lemons here written and it comes back with   120. what it did was it looked for all the lemons  and the corresponding amount next to it and added   it all up so if i look there's three apples here  each at 50 should be 150 if i type apple's in here   it returns 150. so i'm just going to go ahead  and delete this and just add it back here i'm   going to put equal so we're using the sumif so i'm  going to type sum if right here and now i'm going   to walk through what we need to do so what's  the range uh here so the range is going to be   from here to here that this is going to take  place in and so i'm going to put a comma   what's the criterion is  this right here where it says whatever i   choose that is going to go in here so it's going  to be c17 what's the sum range well the sums   are going to be in this column right here so  i'm going to just highlight this and put a comma   and then the last one here and i'm just going to  actually close this up and hit return and you can   see it returned me back to 150 if i type lemons  here returns me back to 120. now we can also do   this now this is some if so we can do this with  multiple columns too and so i'm going to just go   and show you how this one's created i'll put some  ifs here we need to follow along with what it's   going to be telling us so the first thing is the  sum range so where's the sum range this is the sum   range here so i'm going to select my sum range put  my comma what's the next thing i want the criteria   range one so range one is going to be uh where am  i choosing it from so this is criteria range one   this through here and i'm going to put a comma  what's criteria one now well criteria one is the   this spot right here whatever i'm going to be  typing into here and i'm selecting that and comma   well what's criteria range two i'm gonna this  is my criteria range too i'm gonna highlight it   put my comma and now i'm going to put  it's asking about criteria 2 criterion 2   is this spot right here so now i'm going to  close this up and you can see oranges navel   right here there's oranges enable and if i look  there should be one more here and it added up that   and that so if i was looking for apples and then  if i type apples and then over here i type fuji   it should return 50 because there's only one  of them so that you can do this with multiple   columns on this and i just have some other  ones set up for this example in these uh that   you can try on your own but if you're that work  very similar so if you're putting your in our   equals and then you just type your countif  and go ahead and select your count if and   go through and just follow along so we have our  range and our criterion and remember at any point   we have learn more on these and you can open that  up and it's going to explain a little bit more   to you so i hope you this has worked well for  you i wanted to point out one last thing here   for you inside this google sheets tutorial  about functions and formulas and that is if we go   i'm going to actually go to this right here and  there's a couple different ways if we go to the   learn more and we open up the google sheets  function list this will tell you kind of give   you an example description of everything in here  too you can do a search and you can get an idea   of what the different functions can do so take a  look at that because once you start to know the   functions in google sheets it becomes a lot more  powerful for you so i hope you like this tutorial   today on functions and formulas in google sheets  hopefully it came out comes handy for   you let me know if there's anything else you'd  like to learn in google sheets or any other   google microsoft products down in the the  comments down below thanks for watching this   time on teachers tech i'll see you next  time with more tech tips and tutorials