 This video is about the ideal gas law and how you would want to solve the problem involving this using a spreadsheet. Now, there are a number of ways of doing problems like this. For instance, you could go to Wolfram Alpha and if you type in all your numbers and your calculations and you give it units, it spits out the right answer for you. Normally, if you are doing this by hand on a calculator, you want to know your conversion factors to get these into the standard units because most people will end up remembering 8.314 as the gas constant and that requires Pascal's and meters cubed. So you would have to convert those two things there before you did any work with it. Now, that's great. That's one approach. It's perfectly valid and I would definitely encourage people to do it. But maybe you have a problem that's a little bit more complex. Or not this straightforward and you or you want to kind of update your numbers in real time, in which case what's really useful is to have it in a spreadsheet like this. What you can kind of see what I've got set up here is I've got my variables, the numbers, the units and if I change these volumes and so on the result changes here. So I'm here. I'm calculating temperature. So this tutorial is about how you would set this up and a little bit about what these buttons do. So this is sort of a VBA project. So if you haven't got into extending things with VBA in Excel before, you know, this is an interesting starter project. I'm not going to go very in-depth into it, but it's worth doing. So what happens if I click this button is I get, I'm now calculating volume instead. So this is 6,000 Kelvin. Let's reduce that gas to 3,000 Kelvin. The gas contracts. It's now 0.24 cubic meters. And what I've also got on here is a drop-down menu that lets me change the units. Quarter of meter cubed is actually 246 liters. That obviously Wolfram Alpha can do as well. This you have to do a little bit of coding in Excel, but it's sort of a really neat little inroads into what you can do with a spreadsheet to help save some calculations for you. And if you set it up right, it's really, really reusable. So let's open up a sheet and let's have a look. So this is a blank sheet. This is what we would normally be starting with and the way I would set a problem up in Excel would be to do something like this. I know the ideal gas flow has got five components into it. Pressure, volume, amount of the gas constant and temperature. So I'm going to write them here. I'm going to do PV, N, R and T. You can even put equals on at the end. It doesn't necessarily matter. This just tatties up maybe a little bit and then right-align it. It's entirely optional. You don't need to necessarily do that. And on the next column over, I will usually put units. So I might put Pascals, meters cubed, mole, joules per Kelvin per mole. And there's only one unit for temperature, despite what you may have heard. That's Kelvin. Now, this is just an indication for you when you're writing things up. So you don't need to bother going into highlighting these and going into the font settings and changing superscripts because superscripts and subscripts in Excel are actually quite a faff. I wouldn't use it. Of course, you would put it in if you then wanted to present this to someone. You'd export it to a different bit of software, some more processing thing and add your superscripts there. Don't need it here. This is just to remind you what unit each of these things are in. And then we have maybe some default values, three, two, five, one, one. Eight point three, one, four. And let's calculate the temperature of what that gas should be. What we do, what we rearrange, PV equals nRT for a second. Well, PV over nR is equal to the temperature. So I want to do the pressure click times the volume click divided by number of moles times the gas constant, 12,000 element. It's a very hot gas where one mole equals a meter cubed. Normally, it's actually closer to that much when it's much colder. Brand room temperature, actually that's a hundred and six. Maybe it's two, four. Yes, that's about a mole, a volume four room temperature ish. Now that's a bit of a few clicks. So what I'm going to do is delete that cell and type this in again. But instead of using the cells and knowing what references are, I'm going to do something interesting. I'm going to right click on this and go to define name. And what happens if you click on this button in Excel? You can get to name your cell. And in this case, it picks up that something to the left of it says P. So let's name it P and I can locate that machine to cancel it for now. Because what I'm going to do is add all of these at the same time. So we could right click and define name and define name and keep going. Or we can highlight all of them. And it is under the formulas tab. And around this name manager region, we can go create from selection. And this literally creates the names from the selection. And the names for the values are in the left column. It's sensible enough to pick that up. Usually it's at the top row or left column, 99% of the time. Never had data where the labels are at the bottom. So just OK that. And nothing happens. But let's press equals here somewhere and type in R. And what you can see, there's a suggestion that there is a new variable here called R and we press tab to complete that. It now refers to that cell B for 8.31, there we have that's the gas constant. Track that around anywhere it's always going to refer to that same cell. We don't need to bother putting the dollar signs in front to make an absolute reference. And that's a really neat trick. Now, the only thing that you do need to realise that some of the letters involved aren't allowed. So Excel usually appends an underscore to it automatically. It won't allow you for obvious reasons to add a name like B3, because that will then get very confused. So what we can type in here instead of all those cells, if we can do equals, I'm still going to open the brackets because of the orders of operations. And I can do P. Well, there it is, times V. It is divided by N times R underscore. Name it something else if you're not a fan of the underscore. But if you hit tab, it auto completes it. Return that same number as before. And obviously, let's say I double the number of moles in that volume. I get an instant update of what it is. So maybe if you're trying to calculate this and your numbers constantly changing, you're in a lab, set these up and then you can get the instant answer. And well, we could end there. If that's why I would say is the main thrust. If you want to start using a spreadsheet for solving problems, this is how you would set it up. It's the best way of doing it. Make sure you name your values and then your equations that you type in here. Begin to look like the equations that you write down, which is really good. You don't need to keep updating where all your cell references are. Yeah, but let's let's not just end it there. Let's let's put in that unit selector. And for that, we're going to have to go back to a discussion down here for a moment. So let me just a second. There's going to make myself some space on that sheet. Now, the gas constant is usually written down as. 8.314 8.314 and the unit for it is Jules per Kelvin per mole. And if you're not immediately aware of that, Jules is basically Pascal's times meter cube, so we could write down there Pascal meter cube per Kelvin per mole. It would be the same number. Now, the ideal gas flow is there's nothing necessarily special about Pascal's and meters cubed. The point is that this is a relationship and are the units of are are what determine the units you can use for the other values. So let's change this one step at a time. Let's say we're not working in Pascal's. It's a really not a. It's a good unit for doing calculations in, but it's rarely ever used like in a lab. We normally talk about atmospheres because we are in atmospheric pressure. So we're going to convert this to. Atmospheres meter cubed per Kelvin per mole. Well, how do we do that? Well, there are 101,325 Pascal's per atmosphere. That is the straightforward conversion factor. You'd either multiply or divide by that. See if you can figure out which is which before we move on. You would multiply or divide by that number to get a conversion. So what do we need to do? Well, if we multiply it, we go Pascal's times Pascal's, we get Pascal's squared. Not very good there. So what we divide by it? Then the Pascal's will cancel out. And so will this per atmosphere. So what we end up doing is if we divide by 101,325, we get it in atmospheres per mole. So tap that into a calculator. We end up with actually quite a small number. It is 8.206 times 10 to the minus 5. It's quite a small number. But we can't necessarily stop there either. We can actually do the next thing because meters cubed, if we're chemists, we don't enjoy using meters cubed. That's a huge volume. We prefer liters. It's something that we much more accessible unit a liter for a chemist. And well, we know that there are a thousand liters in a meters cubed. So liters per meter cubed. So what we're going to do there? Well, we multiply it, we get liters. And then the meters cubed and the divided by meters cubed will actually cancel out. So if we multiply there, multiply by a thousand, we get a different number. So I'm going to write that one down. We get 0.0821. It's actually, it's that number rounded off. There is an order of magnitude at the end of it. And that is atmospheres liters per Kelvin per mole. So we actually have three gas constants there, depending on what units we're using. Now we could also use Pascal's and liters. And what would happen there is we would only do the times by a thousand rather than the 101325. So we could actually have four possible gas constants. One where we've converted one each, and then a fourth one where we've converted both. And now we can get Excel to deal with that for us. So how are we going to do that? Well, we need to be able to change the gas constant. Let me go back to it. We need to change this gas constant depending on what value we're going to use. So what I'm going to do is put the pressure units up here somewhere. So this bit can be done on a different sheet or it can be done in the corner anywhere to keep it away from the rest of your work. So I'm going to write pressure units here. And I'm also going to write, I'm going to go over two columns. I'm going to write volume units. And the reason I've got the space here will become clear in a moment. So the pressure units I want to use, they're going to be Pascal's and atmosphere. And the volume units I want are liters cubed and liters. What I'm going to do now is make these drop-down boxes that will select them. So I've got my Pascal's here selected and under data, isn't it? There is a tool called data validation. And this is really powerful in Excel for restricting values. If you just want to use, for instance, numbers or you want to restrict someone to just entering integers, this is really useful. And we're going to put a list. And where is the list? We've got a source. Well, the list is here. We've wrote it. So I'm clicked in there. I'm going to highlight that. That's my list. Okay, that's, I'm going to do the exact same very quickly. Go out a list. My source is the volume units. Okay, that. You could also do a defined name range here and define this. Lookup dynamic named ranges before you want to do that. It will be really useful later on when you want to extend this. So now what we've got here are some drop-downs. We click on this drop-down and we can change the unit. Of course, this doesn't actually change anything. So what we're going to do is add a bit of a formula here. So this is 8.314. We could put the other decimal places in if we want, but I'm not going to bother it right now. And if we look back at what our calculations were, I've got them in the corner here. We want to define by a number. Because of order operations, I will do the multiply first. We want to multiply by 1000 if the volume unit is litres. So I'm going to stick an if statement in here. So if something is true, multiply by 1000. So if this cell here equals litres, I'm going to put it in quote marks to make sure it's reading a text string, comma the value of true, we are multiplying it by 1000. If it's not true, if it's meters cubed, we're just going to multiply by one. It's not going to change the number. And then we close that off. So now let's return that. It's not going to change anything because it's meters cubed. But if we drop down this and say litres, it's multiplied by 1000. Now I want to do the same thing, but for the pressure. So I'm going to divide by if, and the test here is if this says atmospheres, again putting it in the quote marks because it's a string of letters, we are going to divide it by 111325. And if it's false, if it's something else, we divide it by one. We'll close that off. Return that. Now nothing changes. But if I swap this to atmospheres and I swap this to litres, well, this is the gas constant. You can actually look at that value on Wikipedia or any other textbook that uses it. Atmosphere, litres per mole per kelvin is an established value of the gas constant. And I would recommend if you're going to use a calculator to actually learn that number, as well as 8.314. It's really useful. The final Excel trick is I want to remind us all that we've changed here. So what I'm going to do is get rid of the jewels. I'm going to go to equals. And I'm going to start stringing things together a bit. I'm going to equals that cell because it's atmospheres. And then you put in an ampersand. There's a couple of different ways of concatenating this together. We'll concatenate many strings together. And we're going to put a space inside quote marks because it doesn't add those automatically. And litres and per kelvin per mole. And I'll stick a space before that. So have a look at that quite closely. It's this and a space and this and a space and per kelvin per mole. Now there's my unit there. So you can do some various other complicated things if you want that to read jewels when it's Pascal's and meter's cubed. Or you just learn the last standard one. And yeah, there we go. That is a way to do it. So there are a couple of other ways of doing this as well. What we could do is we could put a conversion factor up here. So we know that it's 101,325. Well, we could put the, instead of dividing this, we could put it. We could just select it. We could also, if we wanted to use a V lookup or an X lookup. Actually, yeah, let's go ahead and do that. I'm going to change this from a divide to something slightly different. So instead of dividing, I'm going to multiply a number. And so if I want to multiply it, well, I just need to multiply by the inverse of that. So if I stick that number there, that's the number we're going to multiply when it's atmosphere. Actually, that should be there. And then for meter's cubed, we want one and then multiply by 1000. And we could add various other things here. So now instead of ifs, what I'm going to do is multiply. I'm going to use a V lookup formula. You can use X lookup if you're a newer version, but I'll keep it with this one just to show you. So V lookup, this looks down the first column of the table and then returns a new value. So V lookup, I want to look up the conversion factor from Pascal's, from whatever this number is. So the table I want to use is this. I'm going to look down the first column and return the second column. Range lookup. Yeah, false to make that exact. And I'm also going to multiply it by the same thing. Because remember, we multiplied by 1000 earlier, so we don't need to change that. We've changed the division. We're going to look up that number and we're going to look it up in this table. Return the second column. Yeah, we'll make that an exact match as well. Same thing is happening. We're just approaching it a slightly, slightly different way. Once you have 101,000 atmospheres is quite high, so that temperature is off the chart. So those are the conversion factors. And you could put millimeters of mercury in there. Did I write that down or do I need to go Google it? I think I would need to go look it up again, unfortunately. But did I put it on this one here? Yeah, there it is. 1 divided by 133. Let me just put this number in here. So what I could in fact do is change the data validation on this. And change the list to include millimeters of mercury. So now if I do millimeters of mercury... Oh, it's actually not going to do that because I need to extend this down. Again, if you look at what word dynamic named range means, it's a little bit more complicated than I want to get into, but one of those will solve that problem for you. There you go. We can now do it in millimeters of mercury if you wanted to. Yeah, that's centimeters cubed on the right-hand side, everyone, but that'll take a little while. And that's sort of how you'd make a fancy pants calculator. Now the thing is we could actually change that to 298 Kelvin, and then it's not calculating anything else. So let's calculate volume instead. Remember, we have all those named ranges. So we want to calculate volume. It's n times r times t divided by p. And it'll calculate volume for you. Two millimeters of mercury at a very low pressure. So of course, it's going to be a very large volume for just 1.1 moles. So if I change it to 100 mils of mercury, there we go, about a quarter of a meter cubed. I could even then change that drop down to liters. Find out that it's 278 liters of gas. Again, a very low pressure of that 100. 500 mil of mercury. 900 mil of mercury. You can change these any way you like. So if you're maybe struggling with an exact question, maybe you don't want to waste time going in this detail if you're under time pressure. But if you've got past the ground paper or some homework, you want to set things up like this. So if you're doing stuff in the lab, set this up in advance before you go into the lab. Right, so the only remaining thing to do is to have a quick look at the VBA in this. So this is not going to be an extensive version of this. It's just like a starter project. If you wanted to get started in using code to extend your spreadsheets, this would be a good starter. And what we need to do in Excel is access the developer tab. If you're doing this in Google Sheets, you basically have a JavaScript style code that you can use. It will do very similar stuff. So I'm going to insert a button. I'm not going to rename the button just yet, but here we have. I want to assign it a new macro. I've actually written it beforehand. So I'm just going to say this workbook and it's Calculate P. Got OK that. And we're going to BB and look at it. We'll have a look at what the code is doing here. So this, it's calculating P. And what it does is it picks the range B1. That is our pressure. That is where the pressure is. And I'm going to set the formula. So a formula R1C1, it sets what its style is. And I put it equals to that string of letters. N times R times T divided by V. And that is what you would type into that cell if you wanted to calculate the pressure. But in this case, I am setting it using this button click. And it's actually a fairly straightforward thing. You could probably work out really easily how to do Calculate V, for instance, just by looking at that. We'll just copy and paste this and change the code slightly. And if you go to also do range B1 and set the style to output, we'll change the format of it slightly. So I'm going to close that. And when I click this, we're now going to calculate the pressure in Pascal. So this is now swapped. What was originally a number for that there? So I can type in any number I like there. Sometimes I click that button. It then sets it to Pascal's. I can then change the dropdown again to atmospheres. And because that was all taken care of using the gas constant rather than anything else, the macro kind of doesn't care that we've changed the unit. The unit's all dealt with by changing the gas constant. So that's a neat little project if you want to start with. So let's say you copy and pasted that button and we want to change this. Well, we'll want to change this to divided by P, Calculate V. And it's going to be changing it into B2, aren't we? But the thing to probably remember at this point is we may want to reset what the previous one was. So let's set that. We want to set B1's value to whatever its value is. And we want to change it to the style to normal. And we also want to stick that up here as well. So one of the weeks between these two buttons, you're basically going to reset the other ones. Actually, we need to assign the macro here to Calculate V. Now if we click on it, it's going to change to here. So you can see it chewing through all the changes and then setting things. And you can swap between these and we can change the edit the text to just say Calculate, Calculate P, do, do, do, do, get it right. Edit the text, Calculate V. I might be a little wilder to view it. And that'll probably be the starting point. If you want to get into VBA, do look it up. This is a nice starter project. And it makes you a quick ideal gas calculator if you want. Probably just hide these away somewhere on another sheet and you don't need to worry about them ever again. Of course, the main thing to think about is how to extend this idea of using a spreadsheet to organize your data. You want the names of things in a reasonable location. You want values and you don't want to stick in the units here. Excel is not going to recognize you typing in 1.125 atmospheres like that. It's going to think that that is a string of letters and treat it as such. So you want to be keeping that 1.12 and the atmospheres separate. You always also want to keep these units consistent and written down just to the right or wherever is convenient for you, where you can read them. We know that these numbers belong to that unit. So we could even write V equals anti-mills here. And we can set the cell to always be that multiplied by a thousand. That will then always update, right? So we got this 1.2.2 here. The mills will change at the bottom as well. If you want, you could do T and put that as C and subtract the 2.73 from it to get it in center. And that will again always update. So you can check what the temperature in C is. Or you could set that to rely on C. Unfortunately, you can't set it so that they will always update. You'd have to do some magic VBA in the background that will detect a change of cell, but that will be for another time. So that brings us into this quick tutorial. It's just a rapid bit of how do you use a spreadsheet to solve some ideal gas laws or any physical chemistry problem for that matter.