Here's our second example. We have a spherical tank here. This is a spherical tank, it might be a storage tank, and we've got fluid inside of it. It might be a gasoline storage tank or a water storage tank. You've got this side glass, you've got this piece of glass that goes outside. The storage tank itself is metal, but it's got this glass tube, and you can look on the outside to see where the level is. It's filled up in this glass tube. You can see the water level and you can measure the height or the depth. So the depth in this tank is this parameter h. Knowing the depth or the parameter h, you can use this mathematical formula, if you know the radius of the tank, which presumably you do know, and you can use this formula to calculate the volume of water or fluid in this spherical tank. So I've set up the spreadsheet. We have the radius of the tank. You're going to know that, that's the radius. Let's just say somebody measures the height to be two feet. What we want to do is, we want to first set up a calculation to determine the volume in gallons that corresponds to that two feet. Once we have this worksheet setup for a single scenario, we're going to implement it into a one-way case study such that we can know. So maybe we put markings on the outside of this tube for every half foot, then we can know. By looking at this table, we'll know how much volume is in the tank as a function of the height. In this case, it's a pretty sophisticated calculation because we have to implement these conversion factors. The radius is in units of meter. Our depth that we're measuring is in units of feet, but the output we want to be in gallons. So it would actually be quite difficult to put in a single formula here in cell B12 and copy it down. It's definitely possible, but it's a lot easier to set this up as one single scenario, and then you can use a one-way data table to fill this out as a one-way case study, a What-If Analysis. In order to perform this calculation, all the units have to be consistent. I'm going to convert radius into feet. Then we're going to perform the calculation in terms of feet. So this will give us cubic feet. Finally, I'm going to perform a conversion factor to convert that cubic feet back into gallons. So all I'm doing here is mathematical equations. So let's go ahead and start this. In cell B4, I'm going to convert the radius that's in cell B3 from meters into feet. There is a CONVERT function you can put in. So I'm going to reference the cell above it. Sometimes when I'm recording the screencast, it blocks the cell above it. You can't see it. I don't know why that's the case. But we're going to convert the radius in meters that's in cell B3. The CONVERT function you can put in the units you want to convert from, and that's meters into what you want to convert to. For more information on the CONVERT function and the various units that you can use, I would recommend just Googling online the CONVERT function, and you get the Microsoft website and you can get all the different units that you can convert to and from. But we're going to convert the units of cell B3, right now it's in meters, from meters to feet, and that's 3.28 feet, and [inaudible] you probably know [inaudible] there's 3.28 feet per meter. This updates in a live fashion. So if I put in two meters, it automatically updates. Let's put it back to one meter, because that's our radius in this scenario. I'm going to go ahead and name cell B4, rad for radius. I'm going to name cell B5, lowercase h for height. Now we're all set to put in our single calculation or single scenario for cubic feet. So I'm just plugging in this formula down here. So that's Pi times h squared times quantity 3 times our radius minus our height divided by 3. So I'm calculating the volume in cubic feet by using that equation. That's in cubic feet. We want this to be represented in terms of gallons. So you can look up in a textbook, or online, or a resource for how many gallons there are per cubic foot. It turns out there's 7.48. Or we can use the nice CONVERT function, again, CONVERT. We're going to convert the volume in cubic feet that's in cell B7, it's hidden right now, from cubic feet to feet cubed, to gallons, gal. We can use the CONVERT function to do that. That means that if our tank has a radius of one meter, which is 3.28 feet, the volume at a depth or an h value of two feet is going to be 32.9 cubic feet, 245.7 gallons. This scenario updates in a live fashion. If I have three feet in my tank, then it automatically updates to 482 gallons. So we're all set to use our one-way data table tool. Now, this cell B11 is a very special cell. What formula are we going to put into cell B11 to perform this one-way case study? That's right. This is going to be equal. Where on the spreadsheet are we calculating volume in gallons? That's cell B8. So I can press "Enter." Now, we're all set to highlight this region. Again, it has our column input vector on the left. We've got our special cell up there. It's one row up, one cell over. Now, I can go up to the Data tab, What-If Analysis data table. There is no Row input cell for a one-way data table. For this one, this is a Column input vector. By the way, it's far less common, but you can do a one-way data table with a row vector. So it will be a row going across the top. In that case, you would have a Row input cell and not a Column input cell. But I'm going to click in the "Column input cell." Our Column input vector corresponds to height in feet. Which cell is going to be our Column input cell? That's right. Our column input vector is height in feet. The cell that we put that variable in height in feet is B5. So I can click on "B5." I'm going to go ahead and click "Okay." It's going to put every element of our column input vector into that cell or Column input cell, and whatever is resulting in our upper rightmost cell here, when we do that, it's going to put right next to it. So it's going to do one at a time, it's going to calculate the volume in gallons. When we do that, we spit out the volume in gallons, and I've linked it over here to a plot. What this means is, that if you look at a certain depth on this gauge, remember this is going to be a glass tube. Even though the spherical tank is metal, you can't see in it, but you can measure the depth in feet, and then we can convert it using this table or this plot to a volume in gallons. So let's say you look at this glass tube and it's reading four feet, you can go on here, four feet is equivalent of about 730 or so gallons. So hopefully, the screencast gives you a better idea of how to use one-way data tables in Excel. Thanks for watching.
