Okay, so now for the grand finale, I'm going to show you how we can implement a live solution. And, in fact, this is a VBA less live solution. It doesn't require VBA at all, which is somewhat advantageous, especially if you're sending this to a client or coworker. Just as a reminder, this is not a live solution. If I change the length here to something like 15, that does not automatically update our corresponding height values. To do that, we would have to rerun the subroutines that we created in the previous screencasts. So what we're going to do now is we're going to implement what's called a live solution. And what this will enable us to do is we can just automatically change the length or the radius to different values, and it'll automatically update these. We don't have to run any subs or anything. And this is going to be based on the bisection technique. So these are our equations that we used. We calculate the total volume by combining those two. Now, if you combine those two, we get the total volume is equal to this big mess, and that's a function of the height. And we might want to know then, we're trying to calculate h as a function of the volume. So if we're given the volumes like 100 gallons, or we would probably use this in cubic feet, we want to be able to back calculate out what h is. Now, this is not set up for the bisection technique. This is not an f(h) equals 0 format. This is an f(h) equals some constant. So we can easily change this into an f(h) equals 0 format by subtracting the total volume from both sides. And now, this is an f(h) equals 0, because we've subtracted the total volume from both sides. Now, this is f(h) = 0. And we can use this directly in the bisection method. So, let's just go through an example. I wanted to kind of show you what the function looks like for just one of these volumes that we choose. So, I'm just going through an example where volume equals 500 gallons. We have to convert that to cubic feet, because everything else is in feet. So we've got this f(h) equals 0 type problem. We've subtracted the volume from both sides, so again that equals to 0. If you plotted this in Excel, you would get the following. And we're plotting this as a function of h, so h is the independent variable that we're trying to solve for, we're trying to find the root of f(h). And so that would be right here, that's going to be the height then that corresponds to 66.8 feet. And you notice that this has one solution between 0 and 6. In general, we're going to set up the spreadsheet such that the minimum is always going to be 0 that we're looking at. So that's going to be the low end of our bisection method, OEs. And we're not just going to use 6, we're going to use two times the radius, because if we change the radius on the spreadsheet, we want this to change accordingly. So the bisection technique that we set up on the spreadsheet is essentially solving the 0 of this equation up here for each volume that we're using in our case study. So I've got my big equation up here, this is what were solving for. And again, this is an f(h) equals 0 problem. We're going to update this quite a bit, so we're not going to need any of this, just rearrange the spreadsheet a little bit. Now, we're going to set up an area of our spreadsheet that does the bisection technique. So I'm kind of framing out the bisection method area of my spreadsheet. What low, mid, and high really refer to is the independent variable, which is h. So the lowest possible that h would ever be would be 0. The highest, I'm going to put 2 times the radius. So that's the maximum that the height will ever be. We calculate the midpoint, which is just the average of low and high. So now, I've got it setup. So we've got the volume in cubic feet, and I'm going to name that cell. I'm going to name this Vtot, so I can use that in my formula down here. Now, I'm ready to put this big mess into this cell here, and there it is. And you notice that wherever I have h in this equation, remember the independent variable is h. Remember this is what we're ultimately trying to find, so the low, the mid, and the high all correspond to h, that's our independent variable. So wherever I have h up here in this equation, I'm using either the low, and then I'll use, for my next formula, I use mid and high. But this is what it looks like at the very end. You gotta make sure that you're subtracting Vtot, and Vtot was that name cell B7. So I can go ahead and press enter, we're getting a negative. I'm going to go ahead and copy, and paste, and paste. I made it a relative reference, and we see we're going from negative to positive. So somewhere in here, we have a solution. Now, we implement those if formulas that you learn about in the previous screencast into calculating the new low And the new high. And then we're going to copy stuff down, and copy down to 20 iterations, and we will have our solution. So I type in the if formula for the low, if f low times f mid is less than 0, then we're going to use the previous low, otherwise, we're going to use the previous mid. Similarly for the high, the new high value for the next iteration. If f low times f mid is less than 0, that means the solutions in the left half. And if that's the case, then we want the height to be the old mid, otherwise, the solution's in the right half, and we want the height to be the previous height. Now, we're all set. I can take these three cells and drag that formula down, take the f(high), drag it down, and now I can take this entire row. Only the second row, and we're going to drive all the way down to 20 iterations. Now, after 20 iterations, as we see the midpoint of the last iteration is typically what we take, so that's equal to 1.288, and that's going to give us an h in feet. So I'm going to make a quick pointer formula up here, it's going to reference cell H36. So height in feet here is just, I'm going to just put a pointer formula, =, go down to the bottom here, the final midpoint, so that's 1.3. And then we're going to convert that into inches, so I take feet times 12, converts it to inches. So that means at a volume of 500 gallons, the depth will be 15.5 inches. Now, this is the really awesome thing that makes this really superior to anything we did with the goal seek, automating the goal seek and the solver tools. Look what happens when I change the volume. So I'm going to change this to 200 and I pressed Enter, and look how quickly it's going to go through 20 rounds of the bisection method. And it's going to return into this cell here, it's going to return the height. So let me press Enter, boom. We just calculated that in a fraction of a second, so it's a live solution. Furthermore, I can change the length and radius, because everything's live. I can change this to 15 feet, and look what happens at 200 gallons. Look what happens to the depth, it automatically updates. So this is known as a live solution. Now, if you guys know about the data table tool, I'm going to set this up to solve using a one-way data table. I've got my vector of inputs, this is known as a column input vector. And up here, I'm going to put a reference formula, so I'm going to put the output here. And I'm going to highlight this entire region. I'm going to go up here to data, What If Analysis, Data Table, and we have a Column input vector. The Column input cell is where the volume in gallons goes o n our spreadsheet case study, so that is in cell B6. And when I press OK, it's going to go though 33 different volumes. For each volume, it's going through 20 rounds of the bisection technique. And see how quickly it fills out the corresponding height in inches to these different gallons. Ready, go. So it just lightning fast, it calculated the corresponding height for all of those different volumes. And this is a hell of a lot faster than how we did with automating goalsy conserver tools. If I update this to 12 feet, it just automatically recalculates everything. I can increase the radius and so on. So this is an excellent way to implement a live solution if you're doing case studies, if you want to set up a spreadsheet so you can look at different values. You can play around with things without having to run VBA scripts. So I hope you guys enjoyed this, and thanks for watching.
