 Alright, in this tutorial we're going to be creating a spreadsheet that calculates how much collection potential we have given a house or some type of catchment area in terms of total volume of gallons per month. And then we'll also keep building that spreadsheet to look at things like how storage will play with that and how our usage will interplay with that as well. So here I have a spreadsheet I've titled it already so auto save is easy to find calculating rainwater catchment potential. Also give it some type of title, maybe that title will adapt over time, but something like sizing a rainwater tank based on climatological and usage data. And we'll also want to have some instructions, let's put this on this line, we'll come back to those instructions later but I want to make sure I have a spot for them. Let's go ahead and just start thinking about our input, so we'll have user input, and then we'll also have a separate column for the units. Remember that you don't ever want to put units with your number in the same cell because then you can't do math on it. One of those things will be footprint and that's just talking about the base kind of perimeter of the house, that the projection area of your catchment surface. If that's not making sense it's covered extensively in the book to catch the rain. Alright, we'll also want some type of rooftop efficiency. This is just going to be a number usually between 0.5 and 1, always between 0 and 1. Talks about how much rainwater you'll catch from different surfaces. You'll want some type of citation for this. I got 0.95 for a metal roof and my citation is to catch the rain. Here we'll have a note. Maybe that's a note and maybe this is a citation. Let's go ahead and make these bold. We're already starting to get a bunch of words on here and make those bold. Also, eventually I'm probably going to want to have something about the size of my tank, but I might not use that yet. I'm going to put it in because I know I'm getting to it, so a size of a tank will have that bold. These years are input cells. Let's give these a background color so that we can come up with a legend. We'll have the legend way over here. We'll probably move it. I'm sure it'll move over time. Right now I'm going to put it here, input. Let's just copy this format. That's the formatting, input. The footprint, let's start with a 1,200 square foot place. That's foot squared. You could just leave it like foot squared, but if you want to be fancier, there's a bunch of different ways to do it. I have another tutorial on that, shows you how to get all of these different symbols. So for instance, we could take this one here and put it there like that. So it looks a little cleaner. Foot squared, great. There is no units for efficiency. Size of the tank, we're not going to do that yet. We'll come back to it, but we will have it in gallons. All right, next thing we're going to need is some input. So we're going to have rainfall falling onto a surface. Maybe it will have like four arcada as our location, call this the location, and then we'll have each of our months. So it's January, February, March, April, May, June, July, September, November, December. Great. We just dragged to the right so it auto-populate that. Let's go ahead and make all of these columns about that width. We'll also want to have an average and a total column, and let's make those actually all the same with kind of nice and consistent and taking up less space like that. So I just, once I had all those columns highlighted, I could just hover between two column letters and it'll let me change that width of all of them at the same time. And let's go ahead and make all of those bold just using control B. We'll have our rainfall data and we'll get that in inches because we're here in Arcada, which is in California, which is in the US, which uses inches. And I went ahead and looked up some data already and found those. Let's paste values only, found those data there for each of the months. If you've ever been to Arcada, you recognize that, yep, it is really rainy at the beginning of the year, not very rainy during summer. Our average would just be equals average. And we want all those, control shift. And then if you're feeling exceptionally lazy, you could take that. This is probably the silliest way to do it. Paste it in there and call this total. Okay, total, that's not the right function. We want this to be sum. And yep, just like that. I don't know why I pasted with that formatting, so we'll just take this formatting and paste it right there. We are going to want to give it a formatting that's different. These are all input cells. So we'll take this same formatting and we'll make all the months input because you're looking those up and inputting them. Whereas the average and the total, those are actually calculations. So we'll give this a formatting, maybe we'll give it a background color or maybe we'll match kind of how Excel does it and we'll give these the font a color, that was a little too light. Let's give it that color, there you go. So format that there, and format pan paint that to there, and we'll call this output, or maybe we'll call it calculation, I like that. This is our legend here. We will be moving the legend, it's not gonna fit right there. I don't know, it'll be somewhere way over to the right. All right, so we got some good stuff going on. We are gonna wanna have some notes here, some notes and citations. This data here came from the resources that were on the appropedia, let's paste values only, there you go, I don't want that formatting. In fact, let's make this formatting here italicized and we'll also make this color kind of like a dark gray. So it's just a little bit lighter and we'll format paint that to here and we'll call these explanatory, explanatory. Is there an eye in there? I don't know, I'll come back and free that later. So now we get to the pretty complex math part of it, which is our collection capacity, collection capacity. This is the most we could catch from that rain on our roof, and we want that in gallons, I'm gonna go ahead and right justify both of these. And when I do that, you'll see that it kind of cut, no, that's perfect. If it cuts off on you, what you can do is click this. This allows you to wrap or to clip. If you had it clipped, you wouldn't see that end. We want it to just go straight through, call that overflow. So now we have our calculation. I suggest doing this on paper first. The calculation is based on something called V-Rake, volume equals rainfall in inches times our collection area times some conversions, there you go. Our conversions are gonna be one foot per 12 inches and 7.48 gallons per cubic foot. Those two things convert us all into this into cubic feet and then this into gallons, and then we need our rooftop coefficient. So that's the formula, we will make that italicized and we'll also make that darker gray, maybe a little bit darker, there you go. And so the way that formula looks is rainfall. Okay, here's our rainfall in inches times our collection area square feet, that's that, times the one, we need some conversions. So I'm actually gonna stop that and over here I'm gonna put some conversions. So, conversions, conversions and assumptions. I don't know if we need to make any assumptions but I'll put them there. Conversions and assumptions. We're gonna need that 7.4 gallons per cubic feet and the one foot for 12 inches, so 7.48. That is gallons per cubic foot. And again, you could just use the carrot cubic foot or you can use this which says I could copy that or I can try this other one where I type in alt 0179. So here we got alt 0179, let go and there's my foot cubed, great. And we also have 12 inches per foot. And I'm sure we'll have more for the mean time that'll work. Make this top one bold, we'll make all of those I don't know how many we'll have. We'll make those all italicized and the dark gray that means it's explanatory, great. So now we can come and say equals rainfall in inches times my collection area, great, times notice that it's one foot per 12 inches. So what I'm gonna do is say one divided by this. That inverts this so that I have my one foot per 12 inches that I wanted. Times 7.48, great, there's that. Times my roof coefficient, which was this one here. Cool, the good news is, is I already have an answer. It's fourth, let's make this a little bit less decimals here. 4,242 gallons are what I'm collecting. And while I'm there, let's make it clear that that's a calculation using the formatting, get rid of some of those decimals again, there we go. The problem is, is if I drag this right now, so I figured out for January, but if I drag this right, it's going to break because everything dragged right, right, we want January to change to February, that was great. But we didn't want these two to move here and these two to move here. So I hit escape and I'll hit control Z to undo that. There's a couple of ways to fix this. In this tutorial, I'm gonna show you how to fix it using the dollar signs, the fixed references. I'll make another tutorial sometime soon about how to use name cells, which is actually probably my preferred way to do it. It's for sure my preferred way in Excel, and I think it's my preferred way in Google Sheets as well. So what I'm gonna do for the fixed references is everything that I want to stay put, I'm gonna put dollar signs on it. So I want C12 to move, B7, that's my footprint, I don't want that to move. R3, that's my inches for foot, I don't want that to move. R2, that's my gallons per cubic foot, I don't want that to move. And B8, that is my efficiency, I don't want that to move. I'm using F4 to put dollar signs on those. Those dollar signs tell it not to move when I drag it. The dollar sign on the B says B don't move to C. The dollar sign on the 7 says 7 don't move to 8. We're not gonna drag it down, but just in case I put dollar signs on both, boom, hit enter. So now, when I drag this to the right, it all works. If I double click this, we'll see that it's taking November and multiplying it by all those things. Great. My average and my total formula is those don't change, so I can just drag those down. I now have that my average month can catch 2,256 gallons for a total of 27,074 gallons. That is getting us pretty close to a final answer. In fact, this might be a final answer. I'm sure that it could use some formatting. I am going to take this legend and move it way, maybe right to here. I think that I'll fit there. That gives me room to write instructions. Let's make this title the same width as this data below us. We'll do that by hitting merge and center. I'll hit control B to bold it. Oh, sorry, that was just merge and then bold. So here's my center. Let's center it. Let's go ahead and make this a little bit bigger. Maybe even, maybe even 14s go really big. Oops, missed my 14. There you go, love it. You can give it a background. I think for this one, I'm just going to give it a bottom underline and call that good. For my instructions, I'm going to merge all of those things together to give myself room to write instructions. And then let's see what else happened. I have my notes and my citation are a little bit too close, so maybe I'll go there. So now for formatting-wise, let's go ahead and take all of those and put on square borders. And the same with all of these. Border it up all the way around. For these two, I'm going to put one box around those two cells and also one box around these two. And maybe I'll see what it looks like with one box around these two. So it's like location colon. And maybe later, you're going to make it that people could change this arcada to other locations and have data on a different sheet, and it would change your rainfall in inches. We're not going to do that now, but here we'll call this sheet the calculations in case we do add a data sheet later. So that's about it as far as making a table to calculate your collection potential. Make sure you write some instructions in there. You could probably use some cleanup in a few of these spots. For instance, these could all be right-justified. I think that that might look better. This user input, we could bring this in just a tiny bit more. There we go. There. If you double-click it, it's going to go big because you have this collection capacity down there. So instead, we'll just eyeball it to the user input right there. I think that's good. Maybe these numbers are getting kind of big. So what you could do is come in and change the formatting on them. So if you go to Format, we can click the number. And that's going to give us a comma. It's also going to add back on a couple of decimal points. So let's decrease the decimal points. And we're probably going to need to make this total and this average a little bigger in case people have a lot more that they're entering. And then I think it makes sense for maybe these four to be bold. So your total collection capacity here is 27,000 gallons, which is pretty impressive. All right. I hope you enjoy. The next presentation that I'm going to do or the next tutorial is going to be on expanding this so that you can include how much you could actually store. And we'll also include our usage so you know in a month how much you're using because you wouldn't have to store the stuff you're using. Let's go ahead and put in a rainfall graph right here. We have this data. I think it'd be really fun. Let's just take January through December. And we'll insert a chart. It might even predict for us that we want the bar graph. It did. Great, because that is what I want. And it's going to make some predictions, rainfall inches and collection capacity gallons. That's not what we want. It's taking it from those. We could change where it's taking it from Excel or Google Sheets doesn't allow you to have dynamic titles and axes. I wish it did. I'm sure eventually it will. Whereas in Excel, I can set this to a cell. I can't do that in Google Sheets yet. So we'll call this monthly rainfall collection potential. And we should probably give this an access title. Here we go. Change this. We're going to change this to the vertical access title. And here we'll call this collection capacity in gallons. Great. I don't think we need Arcada. For sure we don't need that, because that might change. We don't need to call those months. I think that people will just know that those are months. And we don't need the two different graphs. In fact, I had included the rainfall row when I selected the data. But we're not going to include that on here. You could put it on a second axis. But I think that this is just fine. It gives you a sense of how much you can collect each month. Very visual and also kind of an interesting problem. If you're trying to catch water that you're using in the summer, we'll come back and talk about that more next.