 Alright this sheet is going to be about calculating home water sources and how much water a house is using based upon problem 8 from to catch the rain. And so I don't like switching between sheets and stuff so I'm going to open up a new sheet and then I'm just going to copy and paste in what it is I'm trying to solve. So let's start a new spreadsheet here in Google Sheets. First thing I'm going to do is give this thing a name because I want it to be easy to find in case I shut everything down. So this name maybe is Water, Audit, Engineering 308, Fall 2020 and my name. Alright so now it has the name we're doing great already. Then I am going to go to Canvas. There was the assignment there. I'm just going to take that part of the assignment that's the meat of it and screenshot that using the snipping tool. I'm going to paste that in. Make this small off to the side so that I can find it and follow it later. We're going to be focusing on this one right here which says sheet one book alks. This says the first sheet of the workbook should calculate the water usage based on problem 8. So then I am going to go to the book which I have here in PDF form. And again I'm going to use my snipping tool. I'm just going to snip this right from the book and paste that in down here and make it small but this way I can follow it later. So I'm just going to put this there. So I might leave these in when I'm done with my spreadsheet. I might not but now they're here and I don't have to switch back and forth. So let's do some easy setup stuff. It says name this first sheet book alks. So I'll come down here and double click type book alks. Great. Then it, you know, I'm going to go back to Canvas. I'm going to take this, Control C. I'm going to paste it in here under instructions. I'll probably make these instructions better but now I have something to start off instructions with. And let's maybe make instructions like four cells wide and maybe four rows down and then I can merge and send in those using this merge cells tool. And I also want it to word wrap. So here's my word wrap. And let's make it left aligned maybe it was already. I think that that's good enough for now. We'll do some more formatting later and I'll also probably wordsmith this to be more like instructions for the user to use your spreadsheet when you're done. The next thing I'm going to need is a spot for conversions and this will probably grow as we go along and I'm going to probably get this in gallons per day. And I noticed that I have leaders as one of my things. So let's go ahead and just put in a leaders conversion right now. So you can look that up and then paste that in and then call this leaders per gallon. And let's give it a citation as well. This is from the engineering toolbox or actually that was in to catch the rain. So we can just use to catch the rain like that. We'll do some formatting on conversions and stuff later as well. So there's a lot of ways we can approach this. What I'm going to do is break it up into a few sections. There's this thing that's leaders per flush. So that's like a per use thing. And then there's these that are all flow rates out of faucets. And then there's these gallons per wash things that happen per week. So let's do the first one on sources based on uses per day. Great. And then we'll give a source and usage. And I already know that I'm going to need another column. You might not notice that yet and you might say, okay, usage rate and total per day would be some nice headers. And so your first source is going to be your toilet great. And our usage is two. But you need to, you can't write two flushes per day in the same cell because then you can't do math on it, right? If I say equals this plus two, it's going to be, it doesn't know what to do with that. I wouldn't know what to do with that either. So what we're going to do is we're just going to highlight these and move them over. Or I can take control C or control X if I want and control V. And then here control X and control V so that we can put units on things. So then this usage will be two flushes per day. Whoa, there we go, per day. And our rate is two liters per flush because that's what this assignment tells us right here. So this one is going to that one. And then I'll do some math in a second with my told us per day, but this is already getting a little hard to look at what I'm going to do is take usage and merge and center those or merge and then center those. And then I can do that same thing again, or I can use this format painter, click that and then click here and it'll do the same thing. And I could do it again, click this and click here. So it's done into all of those. And those are probably going to look best if they're bolded. And then let's put some borders on them, like just all around borders. This source is based on usage per day. Let's go ahead and format this too. I often don't format until I'm further along, but I'm just really feeling like I want some formatting. So I merged all those cells. I'm going to center it. I'm going to bold it. I don't know. Maybe you'll go crazy. I'll take the background and turn it dark gray and then take the text and turn it white. So you have this kind of strong header there. Okay. Great. And now first math that we're going to do, I suggest doing this on paper first, but the first math we're going to do is finding the total per day for this toilet. But I really think that this total per day should be in gallons per day. I think it's going to make everything. Most of our other units are in gallons. It just so happens that our toilet was rated in liters. So what we can do is just do this math right here. This equals two liters per flush and we're going to convert that to gallons. To do that, we'll have to divide by this conversion because this was in liters per gallon. Do the math on paper and you'll see that what this gets us is a total number of gallons per flush. And then we can just multiply by our flushes per day. Boom. Multiply by that flushes per day. So now we have a number there. It's got too many significant figures. So I'm going to come up here and this is opposite to how it is set up in Excel, just which direction is which. But we're going to go this way and make this number a little bit more reasonable to look at maybe just like that. And then I'm going to go ahead and add my unit here as well, even though it's in the header. Okay, great. We don't have any more just like this sources based on uses per day, but you could. Maybe you have one toilet, maybe you have two toilets, maybe you have three toilets, so you could keep going down. But we're kind of done there. So I'm just going to go ahead and put some borders on this. This might be too strong of borders. And I'm going to format, get some colors and we'll get a little color key going on. We'll get some colors for our inputs. So let's do orange for our inputs. So here we can have, what would this be, a legend, a color code. And so this orange that we just selected, that orange right there, this will be input. And we'll clean that up in a little bit. So then this one is also, I can just format, paint this to here. This is an input. And then this one is a calculation. And so maybe we want to make our calculations the color of it orange. This is kind of based upon what Excel does automatically. That orange is not quite dark enough for me, so maybe that orange right there. So then this would be a calculation, would be this formatting right here. So we'll go ahead and I'll just format, paint that to there. And we'll format, paint this one to here so it has those covers. Okay, so we have inputs and calculations, great. Now I can give this one a total. It's a little obvious, but here's the total. Let's go ahead and right justify this. I'm going to bold it. This total is just equal to this. We don't have any other toilets. It's just one toilet, kind of boring, but there it is, and gallons per day. And I'll go ahead and put my, maybe just one big box around this so it looks more like a total, so one box like that. And let's go ahead and bold that and also make it orange. So it's a calculation, but it's bold because it's kind of a, whoops, we wanted to do that with, let me control Z to undo that. And we'll do it with this one here, no. So it's that orange, but it's bold, that's a total. Okay, let's go ahead and just copy this formatting. Copy it and paste it right here. Copy, not just the formatting, but everything. The math got messed up, but no problem. We're going to come back and change stuff. This could be sources based on flow rate. So here's sources based on flow rate. We have our source, we have our usage, we have our rate, we have our total per day, this is all looking good. And we have a sink, a second sink, a shower, and a hose. We have four things there. So I could come here and I can right click and say, insert one below. And I could keep doing that, I can highlight both of these and I could say insert two below. So now I have some of my formatting going. Some things got kind of messed up, so I'm just going to take this. I'm just going to drag it down with that little blue box. Cool, all right, let's now fix all this stuff. I'm going to delete everything that's in here and in here because I don't want to accidentally get anything wrong. And instead of this being liters per flush, this is now gallons per minute. Gallon per minute. And of course I can just copy and paste or I can drag down with the little box however it is you want. And this isn't flushes per day, this is now minutes per day. Cuz that's what it's asking for. I'll do this other way where I take this little box and drag it down. Great, and then this was sink. Well, we'll give it a sink one, we'll give it a name, sink one. Maybe this was your kitchen sink, and then sink two, and then shower, and then hose is what all those were. And then coming down, it looks like it was 15 minutes per day, 20 minutes per day, 8 minutes per day, and 10 minutes per day. And over here on the rate in the gallons per minute, it looks like it was 1.5, 1.8, 2.3, if I get something wrong, it's no problem cuz it's Excel and it'll fix it, it'll be easy to fix later. On my total per day, now this math is really easy. It just equals my gallons per minute and my minutes per day, right? Multiply it together, so 15 minutes per day times, whoops, 1.5 gallons per minute. And notice I just clicked in those cells to make them show up in this formula. I hit Enter, and now there it is, 22.5. And then I'm gonna take this blue box and I'm just gonna drag it down. Now, when I dragged it down, notice that the cells dragged with me. There's a way we can stop that from happening if we don't want it to happen, but we actually did want it to happen. We wanted each of these to drag down with us and it did that. And then finally for total, I'm just gonna equal sum, type equal sum. You could also click this E, this epsilon up here. And I'm gonna highlight those four cuz that's what we want our sum to be. Great, so we now have a sum for our sources based on flow rate. And then let's go ahead and just copy this again, this whole thing. Oops, I just hit V, but undid that, Control Z. So, Control C, copy that all again, Control V. And this one's gonna be sources based on uses per week cuz those are the last two things we have. So, sources based on usage per week. And it looks like there's only two. So I'm gonna just take these two, right click and delete those two rows. And now, again, I'm gonna delete the stuff in these boxes so I don't accidentally leave some that shouldn't be there. And my first source was my dishwasher, dishwasher one word, I think so. And then my clothes washer, I think that's what it's called and I think it's two words, but you know, you get the point. And this is gonna be in washes per week, it could be uses per week as well, washes per week. And then these are in gallons per wash. So I'll just Control C, Control V that. And that, we wanted to get us to gallons per day. So there's gonna be some more interesting math in that, we're gonna need another conversion. You may have already guessed it, but if we have per week and we wanna get to per day, the conversion we're missing is seven days per week. Now, you don't have to reference seven days per week and just assume that everyone knows that. And maybe I'll put an S in there so it's plural just because we like to say it that way, seven days per week, great. So the dishwasher was two washes per week, close wash is one wash per week. Dishwasher was nine gallons per wash, close washer 20 gallons per wash. Now this math is not gonna be right. So here, do it on paper for one and then use Excel for the rest. And on paper, you'd see that, okay, if we wanna go two washes per week, and there's seven days per week. We wanna get days on the bottom, then you're gonna divide by this seven days per week. So now we have two divided by seven washes per day and then times this nine gallons per wash. But remember, if I enter this formula, it's gonna work just fine, enter. When I drag it down, it's going to break. The reason it broke is that these two came down just like we wanted to. But this one also came down and we didn't want that one to come down. So what I'm going to do, there's two ways to deal with this. Today, I'm gonna teach you the way where we put dollar signs on it. So I'm gonna use F4 to automatically put dollar signs on it. The truth is, is we only need a dollar sign to be on the three. Because the dollar sign tells the three not to move. You could use F4 to change where the dollar sign is or you can just type it in. So I'll just go ahead without the F4 in case you're not finding the F4. Come here and just type a dollar sign, enter. So that still works. And now when I drag this down, this one works as well. Cuz these two came down with it and it's still stuck on that seven days per week. And this number looks wrong. Let's see, what did I do? Two gallons per wash, I think that's right. Great, and that gets us to our 5.4 gallons per day. This is the sum there. Cool, so now let's just total those things. Let's do a total daily, or let's go daily total, monthly total, yearly total. Those are the three things that we're gonna want. This of course is in gallons per day. This is in gallons per month. This is a little redundant, but it's nice to have gallons per year. So the daily total is just equal to each of these sums. That one, click, hit plus, this one, plus, this one. That's our daily total, 113.4 gallons per day. Our monthly total, I think you'll see it now that we need another conversion for how many days per month. The assignment asks for March, so we'll just put 31 in there and call days per month. But let's make this sell orange so that people know that this is an input. You might wanna change how many days per month you're talking about. So then all we do is we say gallons per day times days per month, that gets us to gallons per month. Write it on paper first to see that that works. And that is too many decimals. So again, we'll come down like that. And then we're gonna need our yearly, so let's just go ahead and put 365 days per year up here in our conversions. And if I wanted to be really tricky, what I could do just for fun, it's a little extra, is I could take this gallons per day and make it so it doesn't move by putting the dollar sign there and allowing the F4 to move. So when we drag down, it multiplied by my days per year. Of course, you could have just said equals daily times days per year. We should make this much prettier. So these are calculations. So we'll give them that calculation color. They're calculations and they're also the final calculation, so we'll make them bold. Let's bold actually everything bold. And let's put this all in like one big final box like that. I like that, cool. So now, oh, you know what? Let's also, let's shift these to the right, make that a little bit better. And maybe we could give these little lines in between. Maybe that'll help people read it. We're right now, we're getting to the point that it's really up to you what looks best. So that's looking good. We'll still need to clean this stuff up here. So that gets us our spreadsheet. Let's clean some of this stuff up here. So instructions, conversions and legends should be up here. Instructions. And conversions, I like to make conversions really not stand out. So I like the text color to be more of a gray and I like it to be italicized. So, and I'm gonna move this legend maybe down to here. And here I'm gonna say conversions, units and citation in case you needed a citation for those numbers. I'm liking that. So then this is the first sheet. So now I can clean this up. This sheet calculates the monthly yearly, the total daily, let's add daily, daily, monthly and yearly water uses based on data in problem eight from the catch of rain. We also need a 2D pie chart. I'm gonna take that out of the instructions because that's for us and not the user but we'll go and do that. Take that out and say enter values in the orange. I'm gonna go to sales to find the totals below. Great, there you go. I think that's good enough. Let's see if I remember vertically aligned. Let's go to the top here. I think that's gonna look a lot better. Okay, great. The only thing left we have to do is to make the pie chart. Maybe I should make that a separate video. I'm gonna do that. Stop this one.