 All right, when we left, we had this sheet that was calculating our collection potential from rainfall on a per month basis. I promised that we'd come back and talk a little bit more about the size of the tank and what that could mean. This is probably going to get a little complex with if statements. If you haven't seen if statements yet, I'll try to make a video on it. Lots of people have great videos on it. They're really fun to play with and an absolute joy in spreadsheets. So we're going to have a tank to catch our rainwater in. Let's say we have a 3,000 gallon tank, of course you'll be able to change that later. Let's type back a little 3,000 in there, 3,000 gallon tank, pretty big tank or small depending on where you're from. Those blue barrels are about 55 gallons. Those white cubes that are called intermediate bulk containers, those are about 275 gallons. This is a pretty reasonable size. We're also going to want to bring in our usage. We're going to talk about how much water we're using each month. There is another spreadsheet video that I did on calculating that. I'll put a link right around here somewhere when you're watching that video. So for our usage, for right now, let's just say that you use 500, I'm just making up these numbers, 500, 500, 400, 500, 700, 600, and then in July, you're like, I need lots of water. That is inconvenient because July, as we saw, doesn't catch much rainwater, 800, 400, 600, 700, and 100. And we can copy these average in totals down like that. So we have our average usage is 558 gallons a month, total 6,700. For formatting, we're going to want this same formatting we have for rainfall because these are user-entered values. Users will enter how much rainwater they're using. We can also steal the formatting from that for these two right there. So it looks a lot like that. Oh, let's put the units right on this usage. So we'll call that gallons. I'll often just use GAL, but if it's so, I'm putting gallons. So there's going to be a lot of decisions we're going to have to make. But let me lead you to where those decisions start. So we're going to have our tank volume is going to be a row. In fact, I think I'm going to do it as two separate rows so we can track what happens over a couple years. And I'm going to start this tank full, tank volume starting full, and this will be in gallons as well. We'll also take the same formatting we had there and put that there. It looks like we're going to have to make this a little bit bigger. Now when I said I sometimes use just GAL, now we see why. So I could come back and turn that to GAL and the same thing here, GAL. The same thing, GAL. And then for the inches, I'll go ahead and use IN, but I'll use the dots so people don't think it's just in. So if it starts full, this cell is just going to be equal to the size of our tank. So if the tank starts full, just type equals, that, great. We're off to a great start. Oh, notice that my spreadsheet is now starting to put or sorry, my chart is now starting to include some of this other data. We'll talk about what that stuff is and probably come in and make some changes later. So if it starts full, now we're going to need to make a choice about where we want our month's data to be. Now of course, the rain doesn't fall just once a month. It's falling over the entire month. And we're modeling this on a month by month basis. This is always a point that you're going to come up to. Even if you're modeling on a minute by minute basis, you still have to decide where you're putting the data for the last minute. So what I'm going to say is that this equals whatever you had last month minus however much you used this month. So we had 3,000 gallons last month. We used 500 gallons this month. And then we're going to add in how much rain we caught this month. Now there are other ways of thinking about this. And I don't think that there's necessarily a right way, as long as you're consistent with it. So this method is saying that we started with 3,000 gallons. We caught, whoops, I hit the wrong one there. It should be this one, collection capacity. We started with 3,000 gallons. We used 500 gallons this month. And then we caught 3,915 gallons. Great, so there's my formula. And then this formula would be ready to drag. But you may have already noticed that there's a big problem with this formula. And the problem is that it is going over the size of the tank. And we can't have more than the size of the tank because that would just be overflow. So what we need to do is come up with a way of limiting this total tank volume to be just 3,000 if it's over. So the way that we're going to do that is we're going to come in and we're going to make this as our main function right here, this whole, all three of these things. This is the thing we're going to be testing against. And I'm going to say if this whole thing, so if the first thing in the if is your logical statement, it tells you right there, logical expression. So my logical expression is if that is greater than the size of my tank. I'm going to hit F4 to put dollar signs on it so the size of the tank doesn't move when I move it. So if this is larger than the size of the tank, comma, the value if true is just going to be the size of the tank again. So I'm going to put F4 to put the dollar signs on. So this says if all of that math ends up being greater than the size of the tank, then just write the size of the tank. If not, go ahead and put in that number. That's the math. So this is the value. If it's not bigger than the tank, put in this. We're still not going to be done. We're going to have to make this statement even more complicated, but I'm going to hit M parentheses so that I now have that done. And as I drag this across, you'll see that now it's capped at 3,000. And some months it goes less than 3,000, which makes a lot of sense because we used more and we weren't catching much. Now there's a new problem that you're not going to see in this yet because it just doesn't come up with the numbers we happen to pick, which is why you really need to test your spreadsheets. So let's say for instance, then in July, I used 10,000 gallons. Well, now we have negative, and you can't have negative. Also, that negative ends up carrying through later on, when really each month should have just been starting at zero because it wasn't zero. So what we need to do is also put in a test for if it goes below zero. And so what we're going to do is where this statement was. This is the one that said just go ahead and write the result. I'm going to hit Ctrl X to get rid of that, and I'm just going to put a new if statement. So if that first thing wasn't true, the value is false, is a new if statement. And that new if statement is going to look really similar to the one we just wrote, except it's going to check if it's less than zero. If it is less than zero, the value of true is just going to be zero. Or maybe you want it to come up with some words. We can put in a test later, or you could put in, you could just put it here. You could type empty, would absolutely be okay. I'm going to go ahead and just call it zero because I want to be able to do math with zero and I don't want my next formula to have to check for the word empty and say if it's the word empty, make it zero. So I'm just going to write zero, zero. Now, if it's greater than the tank, make it the tank. If it's not greater than the tank, we're going to check if it's less than zero. If it's less than zero, make it zero. And if all of that isn't true, we'll finally get to put in that formula that we actually had cared about. So I'm going to drag this over. And now what we have is anytime it goes less than zero, it just says zero. I'm going to take this 10,000 and turn it back to just 1,000. But now we have a test for the whole year. Now, what you might want to do, and what I've chosen to do, is to make a second row for our second year. So that we can see what happens when you wrap from December to January. Or you could just go, you could go January through December and you could just have this going really far to the right. That would work really well. Also, it just wouldn't fit on one screen quite as well. So now this will be second year tank volume. Second year tank volume. And we'll take this formatting again, format, paint that to here. Now, this one is going to be, this one started with the size of the tank. Right there, but instead what we want is for this one, to look at the size in December. Subtract the usage in January and then add the collection capacity in January. So that's where that January stuff that we hadn't been able to calculate in the first row, or row 15. That's where it comes in. Now, unfortunately we're stuck in the same problem where this one has to do all of those tests, the same tests that this one did. Cuz now we need to know if it's greater than the tank, which it is, so we need to fix that, or if it's less than zero. So what I'm gonna do is just take this formula that we just wrote. I'm gonna copy it. I'm just gonna put it right there for a second. I'm gonna come back to it. And then I'm gonna take this whole formula, that thing right there. And I'm going to take this, copy that, and I am going to put it right in here. And then, what I'm gonna do is just take that expression that we just had. And I'm gonna replace each of these expressions with it. So I'm just replacing those expressions, like that. So now it locked it to 3,000. What I could do here, let's see what happens if I drag this one down. Where is that? That says this minus our, nope, I think these two things came down with it. What I could have done on these is on all of my numbers. I don't want the numbers to come down, the letters can change, but the numbers shouldn't for everything that isn't in row 15. So the number 14 shouldn't come down. The number 13 shouldn't come down, 14, 13, 14, 13. The dollar sign on the number makes it so, I'm gonna drag this over, that won't change anything. But now when I drag this down, it still has this usage, or sorry, this usage and this collection capacity. And let's drag this aside. So now what we see is something that looks really normally. You're like, wow, this second row looks exactly the same. So what's the problem? Well, the reason that is is just because we have so much collection at the end of the year, in the beginning of the year, that there's nothing weird showing up. But let's say that in December, we only caught one inch of rain. And in November, we only caught one inch of rain. And in October, we only caught one inch of rain. And also, let's say that our usage in December was 1,000. And let's say in January, we only caught one inch of rain. So now you can see that December ended with 1,589, and January brought us up to 1,799, right? But what if our usage here was 2,000 gallons, right? Really big end of the year party. Well, now we're at 589, but if it was 3,000 gallons, this is at zero. But we still come back up in January because we have so much rain in the winter, we're pretty covered between December and January. I'm gonna control Z and just kind of get rid of all those things, one, one. Okay, now they're back to how they were. These averages and totals, I think, are still useful. You definitely don't need to do this second year. Another way you could have done it is just gone 13 months. That would give you a pretty good sense of what was going on. I'm gonna go ahead and take this calculation style. I'm gonna format, paint that into those spots. So, now we have this way of looking and seeing what our tank volumes are. Now, we probably are gonna wanna graph that. And also, we could put in some type of check to make sure that there is nothing, there's no zeros. Put a check in here that lets people know if any of those months hit zero. We could use conditional formatting, or we could just do something maybe called recommendation, recommendation. And let's give it the same formatting that these had. And here, let's give this one big giant box. We'll put one big border around it and, oops, one big giant box right here. We'll merge that. We'll put one big box around it. And in here, we'll put in a test. And the test is gonna say equals if. We wanna know if any of these things are less than zero. And so what we're gonna do is we're just gonna take the minimum, that's a function. The minimum of that entire array. It gives you examples of what that value should be. So the minimum of that entire array. If the minimum of that entire array is equal to zero, then we knew we ran out of water, right? So that's the logical expression. If the minimum is equal to zero, then comma, value of true, you all ran out of water. You're probably gonna wanna type something else in there that's more official. We can come back to that and do that. And if not, you got water. So we'll test that and then maybe we'll clean up that language. Actually, you know what? You can just clean up that language, make it something that's more appropriate for your usage. So let's make this formatting, the text color black. And well, I guess we should make it explanatory because it is, but now it's kind of a result. Let's go ahead and give it a background that's yellow. And we'll center it and we'll bold it, all right? And now let's test it, right? So let's say here we use 10,000 gallons. Y'all ran out of water right there. Great, and you got water. For this collection potential, there's some cool stuff going on. Let's go ahead and delete this graph and put in two simple bar graphs. So one of them, I'm gonna use the control button in a second. So I'm gonna take my months here, January through December. I'm gonna hit control, and I'm gonna then select my collection capacities. So it's just those two things. So now when I insert a chart, it's really just gonna look a lot like what I want it to look like. I don't need this arcade at the bottom, collection capacity and gallons, that's great. Let's call this something a little bit more monthly rainfall collection potential. Great. And now you could just copy and paste this, but instead I'm just gonna auto do it again. Let's take January through December. And we could do first and second year volumes. Let's just do second year volumes. I think it tells us a lot. So we'll take the months and the second year volumes. We'll say insert chart. And now we have this chart line it up like that. And we'll call this second year tape volumes. This is second year tank volumes. And we don't have to say that it's in gallons because it's down here. So this would be monthly, monthly second year tank volumes. Great. Now we have those two graphs and we could mess around and see what happens when we change these things. You can see the volumes change there. If we change February to be just one inch, that changes February, but it doesn't change our collection because we're still collecting so much and using so little. But if our usage also went up significantly that month, you would then see February just drop out. But we catch a lot of rain the next month. So what if March only had one inch? Well, now we're starting to see some real change. I'm going to undo those changes, kind of come back to our values. Let's make some notes over here in the explanatory region while we're here. This is usage data collected from home. Maybe you'll put in the dates, you know, nine, September 22nd, 2020 by Lonnie. Just so you know where you got that data from. The tank volumes, you can just make a note. These are the first year tank volumes. Maybe you even want to include this formula, what this formula is, just so people can see it if it's printed. I think it might make sense to type it in English. So in English, it would be calculated by last month's tank minus this month's usage plus this month's collection. And let's put it this way. Maybe that's a little extreme. I don't know. And then these are second year tank volumes. And this is a check against the tank going to zero gallons. Of course, we want this formatting all down there so people really aren't looking at that. Let's take this legend and put some square borders on it. Bring it over here. Love it. Conversions, I guess we're done with those. So we can just put maybe a box around those. And oh, I don't love that that is so small. Maybe we'll put it right there so it all fits. Then we'd want to type in some instructions. I also just kind of want that to be probably aligned at the top so it fits with instructions. Maybe we'll put a box around this. And we've been kind of right justifying these things. So that might look a little bit better. Maybe we'll put an underline on all of these things. Maybe all the way to here, kind of like in that. This, these should be italicized. Every, all these notes should be italicized and also in that darker gray. So you could come through and you could start changing things. You're like, okay, what if I only have a 400 square foot roof? Okay, well that changed our collection capacity. It changed, let's see how it changed our monthly volume. So I'm going to undo that. You could see that our monthly volumes are actually getting pretty dangerously low. What if we only had a 250 square foot roof? Well, that's where we just ran out of water. So you can change these variables and see what happens. If you only had a rooftop efficiency of 0.8, not much of a change. It also will help you pick what size gallon, what size tank you want. If you have a 500 gallon tank, you're going to run out of water in July and August. If you have a 5,000 gallon tank, it really doesn't change much from you having an only 3,000 gallon tank. Maybe you could get away with just a 2,000 gallon tank. Nice. So this spreadsheet allows you to make choices. You can put in how much something costs and how valuable the water is for you and find results. Hope you enjoyed. Good luck.