In the previous screencast, I showed you how we can set up one-way data tables. In this screencast, I'm going to talk about two-way data tables. In a one-way data table, you investigate the effect of one input on an output, and we talked about a couple of examples. In this screencast, in a two-way data table or a two-way case study, we're going to investigate the simultaneous effect that two inputs have on an output. Many of you probably know about wind chill. Wind chill is calculated or estimated from this equation here, where we have two inputs that go into the determination of wind chill. This is just a mathematical equation relating temperature, which is capital T and wind velocity, capital V, to the wind chill in degrees Fahrenheit. Really, this is just a mathematical model that has been obtained from experimental data. In the next week, Week 5 of the course, I'm going to kind of give you an introduction to forming these data models in Excel. So we might vary two things at the same time, temperature and velocity, and the contents of this table are going to be the wind chill. We're going to create a wind chill table, so you can look up then, you could look up the temperature outside and wind velocity in miles per hour, and you can determine or estimate the wind chill in degrees Fahrenheit. So that's what we're going to be doing in this screencast. I've got to starter file here called wind chill. I've set this up, so we've got a temperature here in cell B3 that's named capital T, I've got our wind velocity, capital V. Just a note, this equation here, this mathematical equation is only good if the temperature is between negative 45 and positive 45 degrees Fahrenheit, and the wind velocity is between 3 and 60 miles per hour. So just keep that in mind. So what we're going to be doing is we're going to be filling out the contents of this two-way table using the two-way data table tool. We first need to set up a calculation for the wind chill based upon a single scenario. In this cell B8, we're just going to set up that calculation. All I'm going to be doing is putting in this formula here in terms of my named variables for temperature and velocity. So that's what I've done here. I've put in that formula and we want to make sure we double-check that because it's a pretty complex mathematical formula. It looks good to me, so I'm going to press "Enter". That means if the temperature outside is 20 degrees or wind velocity is 15 miles per hour, the wind chill is going to be about minus 6.3 degrees Fahrenheit. We could vary this, I could change these cells, 10 and 20, for example, and it's going to recalculate that because this is a live solution. Let me show you now how we can use this in this two-way table, and you can imagine how difficult this would be to put in a formula here in cell C12 that you could easily copy over and copy down. It's possible, but it requires a lot of mixed cell references, so it's actually quite difficult. But using the two-way data table tool, just like the one-way data table, you set up a single scenario, and then you can just extend it very rapidly and very quickly. So what I'm going to do is I'm going to select this entire region. Before we do that, the upper left-most cell, so this cell, I'll highlight, this is a very special cell because this is, where are you calculating whatever parameter you want to fill out the contents of this table? We want the contents of this table to be the wind chill. So where on the spreadsheet are we calculating the wind chill? That's right. We're calculating it in cell B8, and then I can highlight this entire region here. I go up to the "Data" tab, "What-If Analysis", "Data Table". For a two-way data table, we have a row input cell and a column input cell. We have both of those, it's a two way. The row input vector is the row that's going across our table. So our velocity here is our row input vector. The row input cell is where in our scenario that we've set up for a single situation, where is that? Velocity is cell B4, so I can click on B4. Our column input cell is similar to what we did with the one-way data table tool. Our temperature here is the column input vector. The column input cell then is the variable that corresponds to that. This is temperature in degrees Fahrenheit, so I click that. When I press "Okay", it's going to go through all the combinations of our column input vector and our row input vector. Essentially, it's going to put that into these cells up here, whatever is calculated and is in that upper left most special cell. When we make those combinations of each of these, is going to be placed in the interior of this table. So I can go ahead and press "Okay", and it very quickly calculates the wind chill based upon our mathematical formula here for all the combinations of temperature and velocity. I'm just going to highlight this, and I'm just going to decrease decimal a little bit. We don't need that much precision, so maybe to the 10ths place, and those are our wind chills. So we've created a wind chill table. If the temperature outside is 15 degrees and the velocity of wind is 40 miles an hour, then our wind chill is negative 19 degrees Fahrenheit. Now let me show you how we can enhance this. Maybe we want the user to be able to select a temperature and we want to display as a function of the wind velocity at that temperature. So let's say they choose five degrees. I want to show you how we can plot this, and I've already plotted this for you. In order to use this plot, this is five degrees Fahrenheit, we have to pluck out this row that corresponds to the wind velocities. On sheet 2, if you're interested, you can see how I use the OFFSET function to pluck out whatever row we are specifying. So if I change this to negative 15, we're using this row, and that's on the second sheet, I just put it behind the scenes. So if you wanted to hide this, you can. But if you're interested, you can see how I did that. But we can choose various temperatures and it's going to determine the wind chill as a function of velocity for that chosen temperature. Hopefully, this screencast gives you a better idea of how we can implement a two-way data table in Excel.
