How to Calculate Ratios in Microsoft Excel

hi there welcome to teachers Tech my name is Jamie  and it's great to have you here today I want to   show you how you can create ratios in Microsoft  Excel ratios are a great way to quickly analyze   your data and see the relationships between  certain numbers so if you're able to put in   a couple numbers like this I can show you using  different functions and formulas how to get this   as an output here and you can easily copy this  down I want to show you two different ways that   you can create ratios in Microsoft Excel today  on teachers Tech awesome so the first way I want

to show you is using the gcd function now this  function is the greatest common divisor and I'm   going to separate this oh it is not going to be  just one formula that we're going to create here   with the function we're going to do a multi-step  one here so I'm going to find the greatest common   divisor between these two so I'm just going to  put in the equal sign here and I'm just going   to start typing gcd and there it is Returns the  greatest divisor so I'm going to go ahead and   select that it's going to be the number one is  this one right here so I'm just going to add my   comma and then I'm going to go to number 2 and hit  enter so between these two numbers the greatest   common divisor is 10. and I can quickly copy this  down I'm just going to go ahead and double click   on that green little square and fill it in and  you can see the greatest common divisor between   all these so now I just need to create a formula  formula and I'm going to relate to this cell here   so let's go ahead and start it so we're going to  put the equal sign in and it's going to be this A2   divide it by the gcd here so this one now we want  to have the colon come up so to do this I'm going   to use the Ampersand sign to be able to put this  in so the Ampersand sign is this one right here   above my number seven and then I'm going to go  ahead and use my quotation marks and what do   I want to be put in between the two of these is  going to be the colon and then I'm going to put   my quotation mark again in another Ampersand sign  just like that so I'm not quite done now because   I still have to do this part right here so the  next part it's going to be this number B2 divided   by this again so we have our formula already all I  have to do is hit enter and you can see now I have   the ratio of 5 to 2 and I can Flash Fill This down  I'm just going to go ahead and you can see as it   goes through it has all the correct ratio shows  to all of these right through here now the next   step I want to show you is going to be using the  substitute function and the text function so as I

mentioned I'm going to use the text function and  the substitute function so the text function is   going to convert a value to text and format it  kind of similar to custom formatting in Excel   still the text is a function so I can still use it  with formulas combine it with other functions so   let's go ahead and start this here and I'm going  to start with the text and then I'm going to add   the substitute function you'll see what I'm trying  to substitute so I'm going to go ahead put equals   and I'm just going to go and open up the text  function and start so what do I want to have for   my value well my value is going to be this number  divided by so it's A2 divided by B2 so I'm going   to put my comma and this is where I'm going to put  tell it the format what I want and it's going to   be a division format I can't go and put the  ratio format it wouldn't understand what that   is but I can convert this with the substitute  function that I'll show you so I need to go   ahead and put my quote marks here and I'm going to  put the number sign to show this formatting here   and then put a number another number sign and  then just put another quotation mark and then   I'm going to hit enter so you can see at this  point it just gives me back a five division two   with the division signed here in between so this  is where I want to add the substitution to it so   I'm going to make a modification to this one here  I'm going to go and in front of it and I'm going   to start typing substitute and you see it pop open  right here so I'm just going to add it the text is   going to be embedded in here I'm just going to go  to the outside here and this is where I'm going to   say what I want to substitute now I need to have a  comma here and I'm going to use my quotation marks   put this division sign is what  I want to substitute what do I want to substitute   this with so I'm going to put a comma well I want  to substitute it with the colon so I just need to   use my quotation marks again and then put in the  colon and go ahead and then I'm going to go and   close this up and hit enter so now I have this  in the correct format that I wanted and I can   copy this all the way down so if I just go ahead  and fill everything you can see I get the same   results of the first one in just using this one  step so I started with the text function in this   case and then substituted in where I want it to  be the change of the division sign to the colon   to get my ratio so these are two different ways  that you can create ratios in Microsoft Excel   let me know if you have any other different  method it's down below in the comments thanks   for watching this time my teachers Tech I'll see  you next time with more Tech tips and tutorials