Excellent. I'm starting stuff up here, and then I'm passing presenter over to you. Okay. Well, welcome to class. Again, sorry for the technical difficulties that we experienced this morning, but we are covering today formulas and functions. Formulas are very important in Excel. That's what's going to allow you to add, multiply, divide, subtract, that sort of thing. And one of the things that people need to understand about formulas is the hierarchy of maps. Sometimes people forget that. So let me go over, this is a very basic concept, and that is how things are going to be calculated in Excel. For instance, if I want to say, if I want to put in a formula, and by the way, all formulas start with an equal sign in Excel. So let's say I have some numbers. I've got 100, and 50, and 75. And I would like to add these. To add these, we're using a formula and not using a function. I know a lot of you probably already know some basic functions like equal sum, and we will go into that in a minute. But I'd like to have you understand formulas first because it's very important to understand how to add, subtract, multiply, and divide in Excel, which a function will not do for you. So again, all formulas and functions start with an equal sign. So I'm going to type in an equal, and then let's say I'd like to add the 100. One way of adding it is to just type in 100, of course. And then if I want to add 50, I can type in 50. And then if I want to add 75, I can type in 75. And that gives me the correct answer, right? It's 100 plus 50 plus 75. But there's a problem with doing that, and let's look at that. I'm going to do this again. I'm going to type in an equals. And this time, instead of typing the actual numbers, I'm going to click on the cell that I would like to add. So I'm going to click on B2. Now I could have typed in B2 if I wanted to. But clicking on it will enter it for me. And then I can just put in a plus, okay? And then click on the next cell that I want to add, and type in a plus, and click on the final cell. Notice, I don't know if you can tell on your screen, but the colors of each one of these cells is slightly different. I've got blue, then red, then purple. And that corresponds to the blues and reds and purple that you see in the formula, making it really easy to try and diagnose problems with formulas, okay? Now what a lot of people do is they accidentally type in another plus, and you don't want to do that. You want to just leave it those numbers. So at this point, I'm going to hit Enter. Now they gave me the exact same answer. So why did I put in what are called cell addresses, a cell address of B2, B3, and B4, instead of just typing in 150 and 75? Well, the problem comes if I want to change any of these numbers. Let's say this should have been 55. If I now type in 55 and hit Enter, notice that this formula changed, but this one did not because I used the numbers. And that's why whenever you use formulas or functions, it's best never to use a number. It's best to always use a cell reference, okay? Because what the goal is, is that the data, we'll call this the data. The goal is that the data can change whatever you wanted to, but that the formula doesn't have to. The formula should always be able to stay the same, okay? So we've seen how to add. Now let's say I'm going to do this again. I'm going to delete both of these. I'm going to start out and remember all formulas start with an equal sign. So I'm going to type that in, hit my first one, hit a plus, and then I'm just going to arrow up instead of having to click on it. I'm just arrowing up to the second one, hit a plus, arrow up to the third one, and let's say I accidentally hit another plus. Now I press Enter and look what Excel did for you. It said we found a typo in your formula. That was that extra plus. And would you like me to correct it for you to being this? So if you read this error message that comes up, it'll help you to correct what you got wrong over here. So we'll just say yes, and you see how it automatically corrected it for you, which is a really neat function, okay? All right. So we learned how to type in a formula because sometimes you're going to want to do equal this times this minus that. And if you do that, then you need to be able to use those multipliers and dividers, and you can't use a function for something like that. So notice that I put on my quick access toolbar, the plus, the minus, the multiplication. Notice multiplication is not just an X but an asterisk that's very important to understand. If you look, if you have a numeric pad on your, excuse me, on your keypad, if you look at that numeric keypad, you'll see the division is this forward slash like that. Multiplication is the asterisk, then you have subtraction and addition, which you're used to, okay? So all those things are really good. Now, I'm going to bring up Word for a second, and I'm going to ask you what the answer is to this. If I say equals five plus two times three, what is the answer to that equation? Five plus two times three. Now, unfortunately, I don't get to hear your responses, but what many people would say, they would add five plus two making seven, and then multiply times three which equals 21. Does that make sense? Five plus two equals seven times three equals 21. And yet, the answer that Excel will give you is 11. Why is that? Well, because there's such a thing as the hierarchy of math, and this is explained on page one in the handout that hopefully you have, and if not, Brian will have it for you after the class. The hierarchy of math says multiplication and division always happen before addition and subtraction. So in Excel, what would always happen first would be two times three. Just to make sure people know, the handout is available through the control panel under the handout area. It's called Excel 2016 formulas. It is there. It's downloadable. If you have any trouble downloading it, also please feel free to email me, and I can send you out a copy, but it is there in the control panel. Thank you. So it would do two times three, which is six plus five is 11. And just in case you don't believe me, we'll try it.  I'm just going to copy and paste this. So I'm going to come in here and paste. So five plus two times three is 11. Now let's say you don't want to have to memorize the hierarchy of math. That's fine, but then what you're going to have to do is you're going to have to type in your formula, and you should know whether this is the answer you want. If the answer you had wanted was really 21, then the way to get around the hierarchy of math is to put parentheses around whatever you want to have done first.  Another thing you can do is always put parentheses around things that you want to have happened first, but it's unnecessary. It will always follow the hierarchy of math, which states that multiplication and division will always come before addition and subtraction. Very important concept right on page one. All right, so let's go to a worksheet that has some numbers in it, and I'm going to actually get rid of these. I don't need quite as many numbers as all that. Okay, so, and I'm going to get rid of these totals that are already there, because we're going to create our own. So the way I showed you how to do this before is just type in equals and then click on the first number or type in a plus, that sort of thing. Now if we do that, and let's say we had 100 rows to add, that would take forever. So people would not like Excel very much if that were true. So of course anytime anything seems cumbersome, there's a better way. And the better way in this case is a function. Now on your ribbon, right on this home tab, notice we're on the home tab, we've got an insert function tool right here. This auto, this is a summation symbol, because that's one of the things people do a lot, is to add, summation means to add. So this is one way of seeing what kinds of functions are available. Let's go ahead and click on the down arrow to the right of it. Notice the first thing is to sum. So we can sum, we can average, we can count, we can find the maximum of a range, the minimum of a range, or we can go to more functions and get a ton of other functions. There's about 450 or so functions in Excel. Before I come to click on this, notice the first thing I did was I put my, I clicked on the cell where I want the total to appear. So I clicked in B9, then I came over here, and if I want, I can just click on sum. Now remember I told you that all functions and all formulas start with an equal. So why don't I have to type in an equal? Well, because by clicking on sum, Excel does it for me. Not only does Excel put in equal sum for me, but it also puts in the parentheses, and this is what's called the syntax. This is how all formulas have to be put in. There's always an equal sign first, then there's always the name of the function, which in this case is sum. It could have been average, it could have been max, it could have been min, but there's always an equal, then there's a function name, and then within parentheses, you're going to see the other things that are needed in order for the function to work. So in this case, what was needed for the function to work was the range of numbers that you want to add. So Excel even made a best guess at what that range is. In this case, it's right. It's saying what I think you want to add is b5 through b8. Not only does it put that in here, it also puts the marquee around it and colors it so if you have any doubt as to whether it's right, it gives you a visual right away. So at this point, you can either click on this tool again, or you can just press enter and look at that. This is exactly what you wanted, 3400 equal sum b5 through b8. Now, how do I get this function? We did our first function. How do I get it in all the rest of the cells? Well, you can copy it and paste it if you want, but a lot of you also know that there is a fill handle, this little box in the lower right-hand corner. So I point to that and then drag this over. It fills that function across. You might also know that if I drag this fill handle back on top of itself, it'll delete everything that I just put in. But for now, I'm going to fill this across. Now, the question is, why did this work? And what I'm asking is, notice that the function in this cell, b9, says equal sum b5 through b8. If this says equal sum b5 through b8 and I copied it to this cell, why isn't it still 3400? Isn't b5 through b8 3400? It is, right? So why isn't this 3400? Well, let's look. The only way to answer this question is to actually look to see what's in the cell. And the way to see what's in the cell is to click on it. So by clicking on it, I can look up here and I can say, oh, it doesn't say b5 through b8. It says c5 through c8. And this says d5 through d8. How did that happen? When I just copied and pasted it, how did Excel know to change the column letter as it moved to the right? It moved from b to c to d to e to f, and I didn't do anything to make that happen. All I did was copy and paste. So why did that work? Well, the reason it worked is because Excel uses a concept called relative cell addressing. Relative cell addressing says that as I copy a formula or a function, either one, as I copy to the right, Excel is going to automatically increase the column letter. Because if it didn't, if it left it b for all of these, you could never really copy a formula and have it be correct. Who would ever want to have b5 through b8 here? And b5 through b8 here, nobody would. So it would be pretty useless if it didn't relatively address the cell. Now, relative cell addressing doesn't just work. Let me move this down here. It doesn't just work going this way. It also works coming down. Let's check that out. I'm going to get rid of these. Once again, I'm going to click on my summation tool, or I'll click on the down arrow and click on some. If I'm going to do some, that's the default. So I could just click on, sorry about that.  Notice again Excel guessed correctly that what I want to do is add everything to the left. So now I can just press enter. Now, remember a second ago, I said you could just drag this fill handle. So I could just drag the fill handle down if I wanted to. So what if you needed that to go down 400 rows? That would be quite a pain. So what Excel allows you to do is instead of dragging the fill handle, if you double click the fill handle, Excel is smart enough to know to take whatever you have in this top cell and bring it down as far left as the column to the left of it. That's a pretty cool trick. Again, this was B5 through M5. What do you think Excel changed when it brought it down one row? It didn't change the B or the M because we still want B through M, right? The thing is we don't want row 5 anymore, we want row 6. So let's see if it changed the 5 to a 6. It did. And let's see if it changed the 6 to a 7. It did. So as you bring formulas down, it'll change the row number. As you bring the... Hang on, I'm getting another message. As you bring them across, it changes the column letter. And that's critical not just for when you're copying the formula or function, but what if I moved this whole bit of information? What if I moved it down and across? If this still said B5 or whatever it was, B2 through B5, it would be wrong. But luckily, it changed it from B to C and it changed it to 12 and 15. So in this case, by moving this whole set of data, it kept everything correct because it used relative cell addressing. Okay? So Excel automatically uses relative cell addressing. And it's also explained on page 1 in your handout. Really important concept. Why is it important? Well, we'll see in a second. Let's say we now want to add tax to this total. Okay? So we type in equals and we click on this. And then we're going to multiply. So that is an asterisk. And let's say our tax rate is 10%. So I'll just type in 0.1 and press Enter. And notice it did 10% beautifully and I can drag that across. And it did a great job all the way across, right? But what if our tax rate changes from 10% to 12%? Now all of a sudden, I have to go into every single one of these formulas and change them, right? Because this is wrong in every single one. Remember earlier I told you that we really didn't want to use numbers in formulas if we could help it. We wanted to reference a cell instead. So let's do this. Let's type in 0.1 down here. So notice our tax rate is going to be 0.1. So instead of using the number in our formula, we're going to do an equals and we're still going to multiply that times. Instead of typing the 0.1 in here where it's more difficult to change, we're just going to reference and see if it works. Wow, that looks perfect, doesn't it? 340, isn't that what we wanted? Okay, so now I'm going to drag my cell handle across. Well, once again, this works perfectly, right? So let's see what went wrong. Again, the only way to see what went wrong is to not guess, but to click on this cell and see what happened. If I click on this cell, it says C9, C9, that looks right, times B11. That's odd, there's nothing in B11. Why didn't it just say A11 like it's supposed to? Well, let's go over here. This time, instead of just clicking, I'm going to double click to show you another cool thing that's going to help you solve errors in your formula. I'm going to double click and look at that. It shows me C9 is correct, but now it's C11. I'm going to hit escape to be careful not to ruin this formula, not that it's not kind of already ruined. Come over here, double click, E9 is right, but B11 is not. Why? Well, Excel, remember Excel uses relative cell addressing, so it went from B to C to D, and you were happy about that. It's supposed to and you want it to. The thing is, you don't want that same thing to happen down here, but Excel doesn't know that. Excel is still going to use relative cell addressing so that it's going to move from here to here to here when it references that cell. So this is another really crucial concept in working with formulas, and that is absolute cell addressing. Absolute cell addressing says that the B9, I wanted to go to C9, B9, E9 because I wanted to use this first, then this, then this, right? But this A11, I wanted to stay put. I wanted to be absolute. So when that's the case, you need to go to the part of your formula that you want to have the absolute, which in this case is A11. You want to and you want to add a dollar sign in front of each part of the formula that you want to stay put. Now what some people do is they just put dollar signs around both, hey, I want the column to stay put and I want the row to stay put. But really, because we're dragging to the right, the row is never going to change, right? It's the column that you want to stay put. You want it to stay A. So you would really be just as correct, if not more so, to just put a dollar sign in front of the A because that's the only part that you need to stay put. So let's see what happens. So in our formula, we have B9, which is allowed to change, the A which is not allowed to change, and the 11 which doesn't make any difference because we're not going down any rows. Now we're going to hit Enter, come back up, drag the fill handle. Wow, that looks a lot better. Let's check it out. I'm going to go ahead and hit Enter to go from the first cell to the second and notice that it's still A11, still A11, still A11. And yet it went from B9 to C9 to E9, and on and on to F9, G9. So it did exactly what we wanted. Now because people use the absolute value quite a bit, Excel has given you a function key to put those dollar signs in for you. It's the F4 function key. That doesn't mean you hit the letter F and then hit the number 4. It means along the top of your keyboard, there's F1 through F12. You're going to press the F4. Well, first you're going to make sure you're anywhere in this A11 range. So it's the beginning of the A after the 11. In between, it doesn't matter where. You just have to be somewhere closer to it than to the B9, and then you're going to press F4. Notice the first thing Excel does is put dollar signs around both saying, hey, I'm going to cause both to stay put. If that's not what you want, you can press F4 again, and it cycles through the different options. So first it was both. Now it's just the rows it's going to stay put. Now it's just the column that's going to stay put. Now nothing's going to stay put. So you can either type dollar signs in yourself, or use the F4 function key, which it says in your handout, until you have the right absolute part of your equation. And then drag the bill handle across. Okay? So let's show you another time where you might need to use that absolute. Let's do a percent of total. To figure out what percent this is, the 78,000 is of the 304,000, the way you find a percent of total is you do an equals. You take the number, and you divide by the total. Press Enter, worked beautifully. So 78,000 is about 25% of the total. Now we're going to drag the bill handle down, or I don't even have to drag. I can just double click, remember. Now for some reason I must have had something in here. Look what it did, though. It gave me a divide by zero error. Why? Well, if I double click on this first one, notice it's taking this divided by that, exactly what I wanted. But now if I double click down here, look what happened. Relative cell addressing caused not only this part of the formula to move down, but this one. So now all of a sudden it's dividing by nothing. Again, if I do it down here, double click, I've got that same problem. So if I want to fix this problem, I need to, once again, I need to go to where the incorrect formula is. I need to go to the part of my formula that's got to stay put, which is that, the N5 or the N9. Well, does the N5 need to stay put? No, because it needs to go to N6, N7, and N8. It's the N9 that needs to stay put. So I'm going to click anywhere in there. Now, is it the N part or the N9 part that needs to stay put? Beginning Excel users often just hit F4 and say, hey, I'm just going to make everything stay put and call it good. And that's fine, but it would be better if you just made the part of your formula stay put that you need to. And in this case, because we're driving down, which means the row number is going to change, that's the only part we need to have stay put. So if we want to, we could hit F4 one more time. So now we're saying only the row needs to stay put. I'm going to press Enter by clicking this checkbox right here. So that way I stay in the cell. I'm going to drag this down and Perfection. And we can make sure just by double clicking, we see that now it's going down one for the N6 part, but stay put because I said it has to stay in row nine. Got the dollar sign. Any questions on these really important concepts with formulas? Any question on the hierarchy of math or relative versus absolute cell addressing? Okay, I'm assuming that's enough. So I'm going to go on.  Remember when I put in this first function, I clicked here. I came up here and I clicked on the summation symbol and it automatically put it in correctly for me, right? Well, let's see what happens if I don't have amounts in each and every one of these cells. I'm going to click and you're going to see that if you have a blank cell, Excel could get this wrong. You cannot count on Excel getting it right unless all of them are filled in but you still want to always check. So what happens, so basically what happens is Excel will go backward or go up to the first blank cell it finds or non-numeric cell it finds and stop right there. So how would you correct for this? Lots of different ways of doing that. One is, do you see there's this little box in the upper right-hand corner and upper left-hand corner? I can come up here and I can just drag this box up a couple and notice now it's corrected the formula. That would be a way of doing it, okay? Another way of doing it would be to drag across all four cells yourself. Just drag it and that works beautifully as well. Or you can always come into the formula bar and change this was a 8 and 9 and change this, excuse me, it was a, it wasn't that, it was just the last two cells. It was a 7 and 8 and I could highlight the 7 and make it a 5. So three different ways of correcting it, okay? So what if you don't want to? What if you say, you know what, I just want Excel to do it right? Then instead of just clicking in this cell, if you select all of the cells that you'd like to add plus the cell where you'd like the formula to go and then click on the autosum, it will always do it correctly. Because notice it does B5 through B8 even though you have a hole in your data even though you have a blank.  All right, now we learned to add just one column and drag the fill handle over. But what you could do is you could, let's get rid of all this and all this, what you could do instead is you could highlight all of these cells and an extra row and click on the autosum tool and notice it can do them all at once. Pretty cool, huh? What if, I'm going to undo that, what if you not only want to add up all of these columns but also all of the rows? Well, I'm just going to shift click in that bottom right cell so that I've included not just an extra row but an extra column so my totals can go in both places at the same time and now hit autosum. And it just added them in both places at the same time. In Excel everything can be done so quickly that if ever anything seems cumbersome you're not doing it the right way. Let's show you, I'm going all the way back now to this. So let's say I've got three different ice cream parlors one's in Belby, one's in Sammamish, one's in Issaquah and now I need a grand total of all of them. Okay? What a lot of people do to get the grand total which is completely correct is they'll do equals click on the first one plus click on the second one plus click on the third one and press enter. Works beautifully. So all we did is equals and then we put the three cells in that we wanted. Okay? Another way of doing it is you could do an equals sum now I'm typing it in this time. Notice that when you start typing it shows you a list of different operators that start with sum and if I want I can just double click on it and notice it automatically puts in the less parentheses for me. Now when I have an equals sum instead of having to hit the plus sign I can just click and because this is not next to this one I'm going to hold my control key down. Your control key allows you to select things that aren't next to each other and this is not next to it so I want to hold my control key down and click on that. Notice Excel is nice enough to put the comma in for me. Whenever you're doing a sum of different ranges before we did a sum of a continuous range but when it's different ranges you have to separate them by commas as it's showing you here. Number one comma, number two comma, okay? But I'm not even having to put them in I'm just having to hit my control key even faster because I don't need to hit the plus and then press enter. Notice Excel is nice enough to also put in that ending parentheses for me. So once again all I did was equals sum and I can just type in probably SU before I double click on the sum and then clicking, control clicking, control clicking, and pressing enter. Still though, what happens is this is Starbucks and I only have about, I don't know how many Starbucks there are nationwide. Let's say there are 90,000. Can you imagine clicking on 90,000 rows? So there is a faster way. All you have to do is highlight all the data once again plus one extra row where you want the grand total to appear. So I clicked up here. I held my shift key down and clicked down here because shift highlights everything in between. If I clicked up here and clicked control remember control allows you to select things that aren't next to each other, right? Shift allows you to select everything in between. So I've highlighted all the data and an extra row. Now I'm just going to press my AutoSum tool and see if it works. Let's see. And so just clicking on this I'm going to double click to see what it did. Look at that. It added equals sum B23, B16, B9. It took me two seconds to do something that if I didn't know about this trick it could take me hours to click on 90,000 rows. And the chance that I got things wrong would be probably pretty good. So in Excel it's so worth learning everything you can learn about it because the more you learn about it the more hours you're going to have a free time. Just amazing the amount of free time you will have or maybe not free time but you'll be 100%, 200%, 3%, 400%, 500% more productive because Excel will do the work for you and it will be more accurate. All right. So any questions on basic formulas and functions? So far we've done some basic formulas and functions. Now I'm going to do, so far we've only learned the sum equals sum, right? What if we want to do an average? And we don't know how. Well once again we're going to click in the cell where we want that average to appear. And I think we saw it when I clicked on the down arrow. It was the second one. So I'm going to click on average. And notice it looks a lot like the equal sum does. In fact it looks identical except the function name is average. The problem is that it went one too far down, right? It saw numbers above it and it grabbed them all. So in this case there is, again, there's a little box in the lower right-hand corner although I'm finding it very hard to get to the point of being able to grab it. Yeah, I'm not really able to grab it. So what I'm going to do is I'm just going to drag across the four cells that I really want to average and press enter. So it took all of these numbers and averaged them and then I can drag across, okay? Let's say I'd like to find the maximum. Once again I can come over here and maximum is shortened to max. Once again I don't want to be quite so far down. Drag across just the cells I want to have and press enter. Let's see if it's right. Is 1000 the biggest number? Yes it is. Say I want a minimum. Once again, come up here, go to min, which is shortening for minimum. Drag across just the ones I want and hit enter. And now I can drag them all across by highlighting them all first and dragging them across is going to bring those formulas across to every single one of them. Excuse me, functions across to every single one of them. Any questions on basic functions? Okay. All right, let's say, let me see if I can. Okay, let's go down here. And let's say what I really want to do is count. Instead of adding, I would like to count. For instance, I would like to know how many names there are in this first column. Or a lot of times people do a charity like people want to contribute to, what's a well-known charity? Let's say we're contributing to Seattle Children's Hospital. Okay. And we have people's names here. Sandy, Ken, Sue. Okay. And Sandy contributed 500. Ken contributed 1,000. Sue contributed 2,000. And let's say Andy hasn't come back yet from vacation. He has contributed nothing. All right. So if I did a sum of these, it would give me the 1,000 plus the 500 plus the 2,000. It would give me 3,500, wouldn't it? But I don't really want to know. I know I'm going to put in one more person. Let's say Mary, and she's going to do 50. All right. So I could add these if I wanted to, right? I could just go like that and hit summation. And it gave me the total. But maybe I don't want to know the total. What I want to know is how many people contributed. So I want to know that 1, 2, 3, 4 people contributed because Andy did it. Andy is a zero. Okay? So instead of sum, which adds, there's another function called count. So by start typing in count, I'm going to then double click on that. And I'm just going to select the cells that I want to add. Not to add, but to count. Press Enter. And notice it says 4 people have contributed. If Andy now wants to contribute, it's going to say 5 people have contributed. If these people don't contribute, it's just 3 people. So count is another very useful function for counting. But it counts the cells that have values. Okay? Actual numeric values. So what if instead of counting values, what if you wanted to count alphabetic? Like how many people there are here? Okay? These aren't values. These are alpha, right? Now when I type in count, you may have seen a count A. Count A will count alphabetic things. So double click on count A, select just the name, hit Enter, and it tells me there are 5 people here because I used the count A function. Okay? So count and count A are very useful functions as well as sum, min, max, et cetera. Okay. So I'm looking at my handout now, and we've done page 1, we've done page 2 which talks about absolute cell reference, we've done page 3 which talks about autosum and summing rows and columns. Now you may wonder how if you didn't know about the count function. On page 7, excuse me, on page 6 in my handout, it talks about different ways of entering functions because you may not know the name of a function, right? One way is if you click in the cell where you want the function to appear, notice there's a little function tool right there, an insert function tool. I can click on that, and then you can do a few things. You can look down here to see if a function like count or count A or sum or whatever, you can see if it's already being displayed, and if so, you can select it. Okay. These are recently used functions, the ones that are displayed here. Excuse me.  You can also click on the down arrow and say, hey, I want to see every single function Excel has to offer.  So I could type in count. Notice as I type, it comes down to count, and if I scroll down, I see count A, count blanks if I want, count if, only counted if a certain criteria is true. Okay. So one way is to select all and see everything. But what if you know a little bit more about what you're looking for. What if you know that what you want to do is you want to make some sort of a logical question, like if somebody has this issue, then I want to do that. I want to count them, or I want whatever. So if you want to go to testing things, that would be a logical type of thing. And notice that there is an if statement. Okay. So if you have some clue of what you're looking for, you can select a category, and that just gives you a subset of all. But let's say you have no idea of all this looks Greek to you, and you just say, hey, I just want to know how to calculate a payment for a house because I'm about to buy a house. So if you type up here, calculate payment, it helps to spell right, calculate payment, and hit go. Notice that it starts giving you all sorts of different functions. Now you may not know that PMT is the payment function, but as you scroll through these different things, you're going to see calculate the payment for a loan based on constant payment. Now even at this point, maybe you don't know this is telling you what it needs to calculate. So it says, hey, for the payment, I need to know what is going to be the interest rate, how many periods are you going to pay over? So are you going to pay over 30 years or 36 months or what is the present value and the future value, et cetera, et cetera? And what if you look at this and you say, I haven't a clue what it's trying to tell me here, which I have to admit for a lot of functions, that's how I am. I haven't a clue what they're trying to tell me. Well, the cool thing is you don't have to have a clue. Notice in the bottom left-hand corner, it says help on this function. So you can click on help, and it is such great help. I cannot praise this help enough. So first of all, it gives you a definition. All right, let me try and make this a little bigger so you can see it better. Still sort of making it hard to understand here, potentially. So look at this. So again, it says rate. Notice that some of these have square brackets around it. Square brackets always mean that they're optional. So you have to have these first three, but you don't have to have the second two. Then, let's say you have no idea what NPR means. Well, it says the total number of payments for the loan. So that's helpful, right? It gives you a better idea of those different parts of the syntax that you need. Let's keep going. Okay?  You should read them. But, and gives you tips, which also all of this is very important stuff, but this is one of the things I really love. They say a picture is worth a thousand words, and I so agree that this, if you take this, you can actually copy and paste this into an Excel spreadsheet and then see exactly how everything works. Okay? So if you took this, starting in data, and coming down to the end of the example, oops, that's too far, right? Because I'm starting to get the next one. That's true, let's see. And you'll interest rate. Yes, starting to get the next one. Whoops, come on back. There we go. All I'm doing is shift clicking where I want to end, clicking on copy. Now it does tell you that you want to always start in cell A1 when you copy and paste. I'm going to go into a new spreadsheet and paste. Now the only negative is that, so notice I'm in A1, that sometimes you have to adjust the column width and row height, although look, it did a brilliant job of adjusting it for me. But let me go down here and see, it's showing what the formula is here. Now I'm not sure why they have the results here, but it doesn't have the actual formula in here, but it's saying here equals payment A2 divided by 12. What is A2? Well A2 is the interest rate. So it's saying that in order for you to get a monthly interest rate, since you're working with number of months, whatever your interest rate has to match the number of the, whatever unit you're using down here. So if you're using months down here, you need to use months up here. So this is really important and it was in the tip that we didn't show you, but A12 divided by 12 takes this 8% and makes it a monthly rate. So that's what you need first. That was the rate, remember? Then the next thing it asked for is how many, the end per, the number of periods. Ten was the number of periods, so that's coming from A3. And then last, it needed to know the amount of the loan, which is coming from A4. So even if none of the description was clear to you when just looking at the description, the neat thing is you can look at this and you can see every single detail, and that makes it perfectly clear. Interest first divided by 12, number of periods, and amount. So I highly recommend using this FX and then help on whatever function you might need help with. It'll be so beneficial. Now one of the new things that happened in 2016 is when you type in an equals and start typing in a letter, it will give you all these great different function names. So that would be another way of inserting a function, is to just scroll down and see what function you might want because it'll give you a description. Not only that, but once you get to the function you want and you decide on it, remember if you double click, not only does it put in that left friend, but it starts telling you what it needs. So let's do payments because payment was what we just looked at.  The first thing it needs is the rate, then the number of periods, and then how much money you're borrowing. The present value of how much money you're borrowing. Okay? So not only do you have all of that available, but if you go to the formula toolbar, it will also show you another way of getting financial kinds of functions, logical functions, et cetera. Okay? So page 6 and 7 show you how to get four different ways of inserting functions, and we've gone over those. Okay? And same with pages 8 and 9. Page 10 talks about the if function. The if function is one of many very useful functions. Okay? So for here. So what it does is instead of you having to figure out and look through thousands of rows to determine does this meet your criteria, Excel can do it for you. For instance, in this spreadsheet, I'm seeing whether or not a sales person or a division east, west, north, south, whether they made their goal, which apparently is over 15,000. If so, then I'm going to say great job, otherwise I'm going to say you're fired. Okay? So let's look at that. That's using an if function. Okay? So for a if function, again, it's going to start with an equals and then I'm going to type in an if. If I don't know how to do the if function, I can double click and it's going to say, hey, the next thing I need to know is what are you going to test on? Well, I'm going to see, I'm going to test on whether this is, let's say, greater than. So I'm going to put in a greater than symbol or maybe I can do greater than or equal to 16,000. Okay? Then notice it has a comma right here. Okay? So I'm going to do a comma and then it says what do you want me to do if it's true? Now, for anything alphabetic, I always need to enclose them in quotes. So I'm going to type in a quote and I'm going to say great job or just great. Then I need another comma and then within quotes, I need to say what I'm going to say if it's not a great job. I'm going to say not great. Okay? And 99% of the time if I just hit enter, Excel is going to be nice enough to put in that right print for me and it did. So now I just drag it down and let's see if it works. This was the only one that was not above 16,000. Now let me change that from 16,000 to 18,000. Okay? And drag down. And I've got three that are not great. So this if statement allows you to check for anything. It doesn't have to be greater than. It can check to see if two things are equal. Many times, clients have given me lists. Two different lists. Let's say this is Tom and this is Tam accidentally or whatever. And so they want to say is this equal to that? And if not, tell me it's different. So you can check different, you know, see if data is the same. Okay? So if statements are just incredibly handy, not only for texts like this, but notice this is an if statement. This is saying if F4, so if F4, this one here, is greater than 15,000, then I'm going to give this person a bonus. F4 times 1.2, so I'm giving a 0.2, a 20% bonus. And if not, I just want to put in F4, whatever the amount is. So this person, because they were over 15,000, they got a bonus of 20%. This person didn't do so great. They didn't get a bonus. Okay? This one did. This one did as well. Now you may wonder why did these three not greats? Why did they still get bonuses? Well, that's because here I'm checking to see if it's greater than 15,000, whereas as you recall, here I was checking to see if it's greater than 18,000. Okay? So you can use formulas in the if statement as well to actually make calculations. Okay? All right. Instead of putting this amount in, what if I just wanted it to be blank or instead of saying not great, I just wanted it to be blank? Well, we cannot leave out this last parameter. You have to put it in, but you can just put in double quotes. Double quotes mean don't put anything there. Okay? So now if I drag this down, it's got double quotes, nothing in between. So if I drag this down, and notice that now it has nothing there. Okay? So if statements are very helpful. Now sometimes you want to have, you want to test more than one thing. And in which case, you're going to need not just an if statement, but also an and and an or statement. Okay? So if you need to say, hey, if they made over 1200 here and they made over 4,000 there. Okay? That would be an and statement type of thing. So since we just have a few minutes left before questions, I was wondering, is there a particular function that you don't cover? And if not, I thought one other function I really like. So if so, please let Brian know. And otherwise I'm going to move on to something called a sum if. Sum if says, add only if something is true. So sum if something is true. So let's go back and it looks like trying to find some of this spreadsheet that has some things here. We'll just make up our own. I'm going to take away the total and I'm going to continue this down. All right. So what if I was like to add these numbers, but I only want to add them if there's ice cream? Okay? So that would be a sum if. Sum if if it's ice cream. So for that, I'll take away these two. They have negatives in there. So what I'm going to do is I'm going to type in my equals and start typing in sum. Notice that right below sum is sum if. And by the way, sum if is on page 17 in my handout. So I'm going to double click on sum if. And it's saying what is the range that you want to check? Well, I want to check to see if ice cream is in this range. So I'm going to highlight that range. Then notice after the range it wants a comma. So I'm going to type in a comma. Now the criteria in this case is we're going to put it within double quotes. So I'm going to hit a double quote because it's a text field. I'm going to type in ice cream exactly as it's shown. Another double quote. And then a comma. And then it's saying, okay, what would you like to add if it is ice cream? Well, I want to add this. So if it's ice cream, then it will add these numbers. And I'll press enter. 2100, let's see if that's correct. I'm going to click on the first ice cream. Control click on the second ice cream. Control, there we go. The third, the fourth. And notice that in, this is a really cool thing. In your status bar, notice it's showing me a sum and a count. It's got four items and it came to 2100. And look at that, 2100. Okay? So it's only counted ice cream. Now let's add like that to happen for the rest. Let me go ahead and drag that across. Oh my goodness, we have a problem again. Why do we have a problem? Well, on the first one it worked fine. Let's go ahead and double click in the second one. Notice that the range that it's checking to see if it's ice cream went from being A to being B. Why? Here's another case of relative cell addressing. Okay? We want A to stay put. So I need to come back to my original function. And what do I need to stay put? I need the A over here to stay put. And I need the A over here to stay put. So there's got to be dollar signs in front of those A's. Now do I want the B's to stay put? No, because the next column I want is C, D, E, and F. So it's just this A that I need to stay put. Put on this so that it stays in place. Drag the fill handle over. And now I've successfully added just the ice cream. But probably want to label this so it doesn't think that it's a total of everything. So I'm going to say ice cream total or whatever. And why isn't this column a bit? So Sandy, we have a question here which is can we look at the VLookUp function? Oh, one of my favorites. Well, if it's okay, I would love to do that. And if you don't have time because that's fine, but for those of you who can stay, we're going to look at VLookUp. Here's my VLookUp. VLookUp, for those of you who don't know what it is, because it's certainly not very clear, I don't think, right? I'm just looking at the name, is V stands for vertical. There's a vertical and a horizontal lookup. And what it's useful for is, if you have, let's say you have a sheet of paper that gives you the name of an item, or maybe the name of a legal description or whatever it is, but you've got something, and based on that, so like for a bar stool, you may have what the item number is and you may have what the amount is. And let's say this is an invoice, and you would like to just be able to type in bar stool, and instead of you looking up what the item number and the price is, you just have to type in bar stool and ExcelNow is smart enough to know to look up bar stool over here and bring in the item number and bring in the amount. So it is vertically looking up, it is vertically looking up through this list what needs to be filled out. So instead of you having to do it, it does it, it's so great. Now, because... So a quick note to people as we're running towards the end here. I've got links to the next three webinars in the chat and to where all the archived videos are including today's video. And as a reminder, the handout is there to be downloaded. If you need any help getting it, you can also email brianr at nwjustice.org, and I'll put that email address into the chat box. Thank you, Brian. And also for those of you who aren't able to stay online, I just want to let you know that ZLookup is on page 15 in my handout, so I do have that in there and you can look it up. But anyway, the important thing to note once again is that when it's looking up in this table, as I move these formulas down, I need to somehow tell Excel to please stay within this range. In other words, I need to have absolute cell addressing because otherwise, as I drag this formula down, it's going to start going down here. It's going to look outside the range. There's two ways of doing that. One is inside the VLOOKUP formula, which is equals VLOOKUP. Let's look at this formula. It says equals VLOOKUP, which is just the name, right? And then V6. What is V6? Well, V6 is... Let me double-click in here so maybe it'll tell us. V6 is the lookup value. What does that mean? What does the lookup value mean? Well, what it means is what is it going to get from here to be able to know what to look up in this table? So the lookup value is always going to be the column that it needs here to look up over here in order to get these other values. So that's why it says VLOOKUP, V6, V6 is where it's going to take it. Then it's saying what range do you want me to look at? Well, one way of specifying the range would be to say F... Let me click here. F9, right? Through H13. But in order for it to stay F9 through H13, I would need to make it an absolute value. Instead though, what I've used instead is this... It's called a range name, a range name of furniture. So what I did before I put in this formula is I highlighted this text, and do you see how over here it's already called furniture? But if it weren't, if it were just a range, I would come in here, I click in here, I type in furniture, I hit enter, and that would name my range. And the cool thing about named ranges is that they are absolute. So it makes it a lot easier for you when you're typing in the formula. So it's equal to the lookup. Then what is it that I'm going to look up, which is V6, right? And then I guess if there's a difficult part, this is the difficult part is knowing what to put in here. This last thing that it's going to ask for is, which of the columns do I want to put here? So this is my table here. The first one is always going to be what it's looking at, the bar stool, office chair, et cetera. That's what's going to match, so that's why it's V6. But then this is column one, column two, and column three. And a lot of people say, well, isn't it F9, isn't it G? No, it's column one, two, and three. So what it's saying here is that in the furniture or in this range, I want to bring in the second column, which is the item number. So look at that. So this is going to be identical to this formula, except instead of column two, it should be column three. Okay? So it's equal to the lookup, V6, which is what's going to match this, and then which column? Two over here, three over here. The only problem with that is, if I don't have anything in here, it's going to give me what's called an NA. An NA is saying it's not applicable. It's not finding a blank over here. So until I type in bar stool, until I type in coffee table, or whatever it is, desk, whatever I want to do, it's going to have this NA. And a lot of people don't really like to see that, because a lot of times, like if this is an invoice or whatever it is, it's not going to be filled out. So they want this to be blank until you fill it out, which is why the very next thing on page 16, it talks about if is NA. But I'm going to, this is what you have to do if you don't have 2016. What this is doing is it's saying if, and then it's saying if it's an NA, which means that that NA were to appear, okay, then put in a blank. Remember, two quotes, two double quotes in a row are a blank. And otherwise, do the V lookup. That's what it's saying. So this is saying if there's an NA that appears, put in blank, otherwise do the lookup. But if you have 2016, instead of using the isNA, which is kind of a long formula, right, there is a new, excuse me, kind of a long function. There's a new function called ifNA. Look how nice this is. So remember two seconds ago, we learned equals V lookup, V6, right, V6 has bar stools, furniture, right, and column two. All you have to do now to make sure that NA doesn't appear is put ifNA, and then within parentheses, the V lookup, and a comma, and what you want to have happen if it's false. Oh, excuse me, that's true. If there is an NA, you're saying put in a blank, but if not, this ifNA just says okay, then just do the V lookup. How cool is that that you now have this shortening of being able to do that? So look, the difference is, the only difference is when I hit delete, instead of seeing all those NA's, you're just not seeing anything. And now when I type in barstool, it's beautiful, okay? So ifNA is something you can use, and it's just basically nesting or putting one formula inside of another, okay? Any last tip you would like to end on here, Sandy? Yes, maybe one of the last tips if nobody's got a question.  Equal sum is what people use, a ton of. And so let's say we've just got some data here and some, I'm going to do my ice cream, cherries. And what I really want to do is I want to put a total here and there. So I'm going to type in total, hit control enter, so I get it in both places. I'm ready to do that, but of course if I hit my summation symbol, it has no clue what to do. So with that, and then I would have to drag across what I'd like to add, right? And then I'd have to hit enter and I'd have to pull it across. Which takes a little bit of time, not terrible, but more than I want to spend. So instead what I do is I click in that first cell and I shift click in the last cell, so I'm not including the totals. And I put in just a dummy number like 50. And in order to get it in all those cells, I press control enter, so I highlight the area, I put in a dummy number and I press control enter. Then I press shift and click in, remember one additional row, one additional column, hit the autosum. Because I've got numbers in it now, it does all the work for me. So all I need to do now is shift and click one back and press delete. And I'm ready to go. So I like that little trick of filling in dummy numbers so I can get Excel to put in my formula for me and then just deleting the dummy numbers. So again, all I did was highlight as much as I wanted, typed in the number, and instead of pressing enter, I press control enter, which puts my information everywhere I want. Thank you so much for coming. It's been a lot of fun and look forward to our next training together. Excellent. Thank you so much, Sandy. I greatly appreciate it. We will be sending out links to the email list and putting together a blog post. The booklet is downloadable and will also be published online. Until next time, thank you all.


at all times, all right? Make a little mark that you can see from the 25, and then zero with that, okay? And then when we get into reflexive fire, make a hytheracic kill box. The port of this drill is recoil control. All right? Are we ready on the left? Are we ready on the right? Are we ready in the center?


Hi, my name is Andrew Vargo with Razor Back Offroad and today we're excited to do an install video for a fixed windshield for the 2021 to current Razor Trail and Trail S machines. Now before we begin I'm going to go over some of the tools that you'll need for the install. So I kind of have divided them up into sections. So for this first section this will be the lower mounting brackets that you'll be mounting onto the windshield and you're going to need a 7-16 box wrench and a Phillips bit. And this is all the quarter inch hardware that we'll provide for you. And then the next part of the install which will actually mount the windshield to the lower roll bar, lower part of the machine on the roll bar, you'll need a 15 millimeter socket and then we have the bolts that you'll have to replace the lower bolts on the machine and we've provided that hardware and then the last part of the install will be the 5-16 hardware and you'll need a half inch socket and a box wrench for that. And I use a power drill, it just makes it faster just for these install videos but you can use hand tools if you don't have any drills or power tools at your disposal. So we do include a couple of rubber plugs here and these rubber plugs are meant for the Polaris aluminum stock roof. So the one thing that's nice about this windshield is that it is compatible with both of the Polaris stock roofs. So there's the plastic roof and then there's the aluminum roof that Polaris manufactures. So these plugs are used for that and if you visit our website you can see some photos and images of how this is installed onto that roof. So I'm going to go ahead and begin. Like I said, we're going to start with this first section. So to begin I'll flip over the windshield and it'd be nice if you had a workbench to work on but you're going to grab one of these brackets and you want the slot high and you want these other slots lower. So if I lay them down on the table you can see that they're tapered upwards. So this is the driver side and this is the passenger side and they're going to go onto the bottom like so. And so to install you're going to need one of these bolts. Put the washer on the bolt coming through the bottom and you're going to put the washer and then the nyloc nut on top. And you're going to do the same thing to the other slot. All right and like I said just leave everything hand loose because that's how we're going to leave it and then we're going to once you're done with that now we're going to go ahead and put on to the machine but before we do that you're going to need that 15 millimeter socket and you're going to want to you're going to want to remove the bottom bolts of this rolled cage just the bottom bolts and you're going to want to make sure you save the stock nyloc nut that's on the that comes stock on the machine. So I'm going to go ahead and remove those. Okay and then put this off to the side. Okay I'm going to grab the windshield and you're going to install it onto the machine so I kind of just like to do that and you just make sure the lower brackets are in place and that the panel is flat onto the roll cage like so and this window does have some tabs that are bent in the back and you just want to make sure that they're underneath there's a welded on bracket right here on the roll cage you just want to make sure that they're the tabs are underneath here and you'll you'll notice it too you'll see a big gap on the side here and or over there and you'll notice that if the tabs aren't tucked underneath there this will be sticking up and it won't look right so once you have that okay you're going to grab the two lower bolts that we provided for you and you're going to go ahead and install the lower bolt in place of that stock hardware bolt that you just took off earlier okay you're going to go ahead and tighten that down okay and once you're done with that we're going to go ahead in the next step and tighten these lower bolts here so for that so you're going to grab the Phillips bit and the 716 box wrench and you're going to come to the machine and you just want to make sure that the lower part of this panel is is sitting flush and it looks like it's following the contours of the machine and I always start with the lower bolt and you kind of need to look in here to see once you have that tightened down you're going to do the same thing to the other side so once you're done tightening the hardware on the lower bracket one thing before we move to the next step that I want to explain is that you can install this windshield obviously with or without the roof but if you do have the stock roof and you want to put it in I would say you'd want to at this point install it because it has some mounting brackets that come up and through here and we've designed it to have enough space up here at the top for those plastic brackets on the plastic roof to fit through here so at this time you'll want to want to pull back the windshield just a little bit and feed those plastic brackets through here and then you'll pull it down and it'll pull everything together and then you'll use the last pieces of hardware that we've provided to finish this windshield off so we'll go ahead and move to the next step you're going to want you're going to want the half inch socket and the half inch box wrench and you're going to want to grab one set of hardware just down and for this you're going to want the bolt and the washer to be the closest towards you and you're going to want the nyloc nut to be up and underneath and should be able just to install it go ahead and tighten that down okay you need to do the same thing to the other side so once you're done tightening all the hardware down and you're happy with how your windshield sits that will conclude the installation of the fixed windshield so once you're finished tightening all the hardware on your windshield you're happy with how it sits on your machine I'll talk a little bit more about why we designed it this way we designed this fixed windshield because it was really difficult with our folding windshield to get it to fit with the stock roofs that Polaris makes and so this was our solution to that that problem is that we wanted to provide you guys with a windshield that will be compatible with the Polaris stock roofs so this will fit with the plastic and aluminum stock Polaris roof but this will also fit with razorback off-road's roof as well so if you have any questions please contact our sales team at razorbackoffroad and visit our website at razorbackoffroad.com as well to see machine compatibility or any other information you may be looking for about this windshield well that concludes our installation video for the fixed windshield and other than that we hope to see you out on the trail


I mean I think it's new year so that's kind of the focus we've got you know quite a few new guys so just trying to get everybody comfortable we're you know fortunate this year we've been able to get on the ice a little bit with the team here early on and we've just been trying to you know work on some skills work on some habits to establish a foundation for when we start I mean the focus is just on us here to start to get us you know get us going and establish who we are and create an identity for for our group and then I think as we get through the two weeks we know we'll start to start to dive into to Canton a little bit but you know for us to be successful it's got to be one week when we get the time okay you know in terms of just our schedule we've worked hard to you know to build the most competitive schedule that we you know that we can you know our league has has grown and expanded to become you know much more much more competitive you know we're we get to play at the Christmas tournament this year and us we go we're going out to Adrian again we're also gonna play the USA team program which will be a great opportunity for you know for our program I mean they'll have countless you know first and second round HL draft picks on the team so it'll be a good opportunity for you know for us to you know again to continue to grow as a group and continue to grow our program


what team and head coaches, first team coaches are starting to realize is that there's specific athletes or players within a team environment that need that individual attention, that individual or specific help. So that's why now you're going to start to see first team coaches in in the professional game, whether that be in soccer, whether that be in basketball, whether that be in in the NFL, American football, baseball, whichever sport it might be, you're going to start to see these individual development coaches who are going to come in and work with players on an individual basis, right? So they are part of the actual coaching team of the first team environment, but essentially what their role is is they're employed to help players on an individual basis because there's certain things that they that head coaches can't work with them on when they're working with a team. Also at the pro level, head coaches are normally very very busy so they don't have the time to dedicate to working with players individually, which means bringing in an individual development coach, which essentially is a private trainer, is a solution to to their problem. Now what relevance does this have with your coaching business, right? So if you're a private trainer watching this, then you should be making note because this is going to be the future of first team teams, right? And what's going to start to happen is this is going to start to trickle down into the grassroots and youth sport. So where head coaches want to try and get an advantage over other teams, over other clubs, they're going to start to bring in these individual trainers that are going to work with, it might be their goalkeeper, it might be their defenders, it might be their strikers, right? They're going to hire out these individual trainers to come in and work with specific players on specific things, okay? So if there's an athlete within that team that is really struggling and that coach doesn't have the time to work with them, then they're going to start to hire out trainers to come in and specifically work with that individual, right? So wanting to make this video because it's a message for everyone out there that this industry is going to get bigger and bigger, it's going to get more professional, right? Now you're starting to see football associations, like for example FIFA, UEFA and other organizations who are starting to now create webinars and courses on individual training, right? So this industry is going to start to continue to grow, it's going to get bigger and bigger and essentially it's going to become more professional, which means there's going to be a lot of great opportunities for great trainers that are maybe in the youth setups that want to go into a first team environment and just focus on the individual training, okay? Because if a huge club like Arsenal have realized, you know what? Our first team players, right, need this attention, need this personal help, then that is a message for other teams out there, that if this big club needs those individual trainers to come in and work with their athletes, then that could be the future and that could be a way of teams getting an advantage over other teams.


So back to basics, I'm going to be looking at the service level data, the basic basic data that you collect yourselves, what you perhaps already collect as a routine or what you collect as specific to something that you need to know more about. So I've just split it into basic types of routine data and sometimes people can forget that some of their routine data is actually very useful if you start delving into that. And specific data, that's the stuff that you might be thinking is important. Okay so yeah, everyday data, all the stuff that you usually collect as a routine, usually most people collect demographics of some sort, you're collecting ideas on how many people are using your service, what aspects of this your service that they're using, you're collecting financial data, so you might have data there available to work out what the costs of your service or split that into different elements, what parts of your service are costing the most or the most efficient. Hopefully also you're collecting some idea about the specific needs of your service users, particularly those around your particular service. So if it's a housing service, for example, you might be looking at what their current status of housing is and what they need. So specific data might be things that we're looking you're looking for effectiveness. So you want to be looking at your outcome success rates. So whatever your your service is designed for, hopefully you're measuring how many people use your service with a successful outcome. Again if it's housing, have they improved their housing level in some way or another? Then there are quality measures, how well are you delivering your service? So you're probably already collecting some kind of evaluation from service users or satisfaction surveys from service users. Not necessarily service users, but it could also people who are commissioning you to deliver that service and how well they are seeing your efficiencies. And then your efficiency measures such as did not tend rates might be particularly important to you. So I'm going to split them up into two kinds of knowledge that what we want to know about in a service that is who's benefiting and how are they benefiting. And when I do my second presentation I'll give you a good example of how that information can actually be quite useful, but also quite controversial. So it usually relates to your service goals, your outcomes and impacts. Some people get confused about the difference of those things or trying to separate those out as well. Basic outcomes are successfully used the service or successfully benefited from the service. And to get some information on that we need to collect the basic demographics and things that are pertinent to your service. As I say age and gender are typical type of need for that person. If I was running let's say a health and social care service I want to know what their specific health needs were, their social needs, what they're using service for. Then as we've seen that geographic data can be very important so postcode area is very useful to see how much you're covering and whether you're covering people with a particular need. So socioeconomic status example. So how do they benefit? What is the effect of the service? Did it work? Is it working for them? Is it meeting the need of the data you've collected on need? Is the service actually meeting that? And sometimes quite tricky to ask especially quantitatively how did it work? Sometimes you might need to explore that in a qualitative way. Okay it did work somebody moved from unsafe housing to safer housing but you want to know why? What was it that gained that improvement for them? Also importantly what did not work? And again a why question is very often something you can explore qualitatively. Okay so outcomes and impacts. The main outcomes are probably something to do with your what your service delivers. So example here is training community members for delivering mental health first aid. So improving the referrals to services, improving early early referrals might be a particular outcome. Reducing that's a deliberate self-harm in the community. Seeing more self-help in the community might be your identified outcomes. But the impacts of that are technically related to the outcomes. The impacts would be things that are slightly harder to measure but you can measure them specifically by identifying these. So there might be other benefits than knock-on effects if you like. So you've now got a wider community understanding of mental health. There might be an improved care pathway so that statutory services are seeing earlier referral or better referrals and they're seeing an improved uptake of services. There might be increased community cohesion and collaboration and maybe mutual aid groups have started up. So these are sometimes I call them spin-offs from your outcomes and it's showing that the impact of you achieving your outcomes is actually having impacts on wider issues. Okay so what do you do with the data? Now this is something that I'm going to go through in the second presentation but I think in my main message here is that those basic data collection are actually gold dust. You can do a lot with the basics. I think sometimes especially smaller community enterprises, charities and self-help groups can think that all this data analysis is beyond them. It's too complicated. You've got to access as they say the sexual data which is useful but sometimes you just need to look at what you're doing and you realize that you've got a wealth of information there that you could do a lot with. So your basic analysis is what is working? What is it that your service is doing that is actually having a positive impact and outcome for the people using the service? It's important to look at who it works for. So that collecting of basic demographic data, you're looking at not just who's using the service but be mindful of who's not there, who you not be able to reach, particularly if you've got a service that's price itself or has a mission statement to reach out to vulnerable people, to social excluded people. It's very easy to pick the low hanging fruit as some people say but are you actually achieving reach to the people that you want to reach? How it works. Sometimes you can collect that data and analyze that data quantitatively so you can see what aspects of your service are impacting on the people with most need for example but sometimes those how and why questions are more easily accessed by using quantitative or data that's about people's experience. So you're asking people what was the best element for you and things like that. Also of course if you're using, if you're collecting good financial data you can then break that down into seeing how much some of that success costs and then you can do very simple analysis of your financial efficiency, how much value for money you're giving people who are either commissioning you or funding you and people like that. Funders like cost-benefit analysis. Okay I'm going to finish it there and go into it a bit more depth in the second presentation but this is just food for thought thinking of valuing what you're already collecting and not needing to make it to that conjugated. I think we're going to be answering questions later but I'll have a look at the chat. Okay but now we're going to hand over and see a couple of case studies. So first we've got Mark from Circle telling us about his service and the data they use. Thanks Mark. Thank you I'll just share the screen. So presumably everybody can hear. So I'm Mark Wynn and I'm from an organisation called Haywood Middleton and Rochdale Circle. My colleague Leanne the lady who won the quiz is also on the panel. She will answer any questions that you want to put on the Q&A part in the chat sort of box if you will. So basically what does who we circle what do we do and normally when I do a presentation I start with a little quiz and I wasn't going to do that today but then I sort of realised yeah he sort of explains exactly who we are and what we do and why data is really important. So the three quiz questions and you can post answers in the chat is where would you go for a coffee or a meal with your friends?  So where would you go for a coffee with your friends? What year did the sex pistols play Manchester Leicester Free Trade Hall and when did the Beatles play the last concert at the Cavern Club? And so just going back to what Circle does we're a membership organisation for the over 50s. We operate predominantly in the Rochdale borough but we've got we've edged into areas of Oldham and Interbury. We're launching a North Manchester Circle with partners in Mossen and New Mossen. We have a version in Haringey in London and we've done some stuff in Trafford and we've got some other interested parties going along but basically we put on social events that members asked to do and they arranged for a meeting up for a coffee, going out for a meal, walks, talks, quizzes, that sort of thing through to as you can see on the slide hot air ballooning across the Lake District and indoor skydiving and on this side on the bottom right you'll also see a lady, one of our members who absailed down Rochdale Town Old Clock and we're really successful in what we do. We get really good attendances. The members suggest the event so then we facilitate that so it's very much member driven. We do the things that they asked to put on the social calendar. Sorry to interrupt you on our slides showing. All right. Miss, I'll just come out of that. Sorry it might have felt that. I didn't show the, sorry, apologies for the technical. We did practice this yesterday so again apologies for this. So yeah, so I'll stop through the slides that I mentioned. So yeah, so we put on a social events that members asked for and as you can see from the slides now, they're really broad range and they're really popular, well attended and that's driven by the fact that the members suggest the things that they want to do. The other part of what we do is practical. So we put on practical support, so gardening, decorating, DIY, moving boxes, changing light bulbs, all the jobs, all the people struggle to get somebody out to come and do. We have a team that delivers that. We also operate in a befriending program which we did. We're doing partnership with Give and Take Care which is based at Brunel University. It was developed along with Professor Heinz-Wolf, those of you who remember him. Unfortunately he's passed away now but we developed a unique befriending scheme along with that. And then the final thing, the final core part of what we do is the volunteer driver service. So volunteers drive as a service, take people to hospital, doctors, dentists, that sort of activity as well as to social events. Obviously during the COVID we've had to repurpose a lot of that and so we do collection and delivery prescriptions, emergency and standard food shops, delivery of PPE, information packs, craft packs, that sort of thing. And we've now been able to reintroduce some level of passenger transport. And then they're all great things but we always ask the question at Circle, does Circle work? Do all them nice things? Do those go in it for a coffee, go in it for a meal? Abseiling down Rochdale town or clock? Do they make a difference? And initially we interviewed over 200 of our members. It was an externally verified survey and you can see that the scores are really good, nearly 80% report increased social activity, a similar figure for over 50% improved self-confidence and improved health and well-being, 14.4% reduction in GP visits. Some really good numbers but we were conscious that we asked that question and I suppose if your child comes home with a cake that they've baked at school and you taste it and they ask you if it tastes nice, sometimes you might not give the real true answer and because we were asking the question we had to be sure that we were getting an honest answer. And so what drove us to data was we were contacted by a university studying social isolation amongst all the people and they said we were the best example they could find worldwide and this is just four people based in an office on Oldham Road in Rochdale. So they said could they come and see what we do first hand and meet members and talk to members and we said absolutely and the university was the University of Osaka in Japan. So they flew into Rochdale, came and saw what we did and they wrote a glowing report, sent it to us but they sent it to us in Japanese so it took two months to get that translated but what they said it was they didn't feel they'd walked into anything clinical it was very like walking into a group of friends out on a social event. So we were really pleased with that but that sort of planted the seed of does circle work and how can we prove that. So we got in contact with universities and I think it was as Lucy has just said most organisations certainly of our scale haven't got a research department. So we worked with the University of Manchester and also Manchester Metropolitan University who have a fabulous Q Steps program. So for the last this is our fourth year now with MMU we were able to get a student doing a quantity study on the impact of our work. So year one was the impact of being a member of Circle, year two was the impact of being a member of the Volunteer Drive Service and year three was being a volunteer with Circle. So all that research we would never have the time the opportunity or the work with all if we're perfectly honest to sort of pull together. What that research does it gives you an analytical and as near unbiased analysis of what you do as you possibly can by doing that you obviously open your or expose yourself to possible criticism all them nice things they may not actually work. Luckily the majority of the reports come back with really positive stuff. What they also do is give you advice and recommendations on the gaps in your system. So that is vital in terms of accessing funding. Subsequent to that we've done reports on special projects that we've done with Rochester Barrow-Wide Housing in their independent living scheme. How we expand the model and monetise the model and make it sustainable. We've also done some stuff with the University of the United Nations based in Brussels around our use of tech. So we're a big advocate of the use of tech in all the things we do. We've also done some stuff around co-production and we were featured on people powered projects so people were the members drive the decision making of the organisation.  As an organisation only about 20 percent of our income comes from the local authority. We do contracted work with different people because we can evidence that what we do makes a difference. I think that's the key to why you would use data. It makes a difference to people's lives and it also helps you to sustain the organisation because at the end of the day the only way you can do good is to be there and the only way you can be there is by making sure that you can pay your bills. So that all this university workers led us to get a huge amount of recognition for again just this small organisation based on Aldermen Road in Watchdale. If you go on the government website for CICs we're held as a best practice. We've done different rewards. We've done stuff that we're recognised alongside the private sector as innovators in our field. It led to us being the great amount of social enterprise the year at the end of 2017 beginning of 2018 and obviously that's all that ties back to working with the data sets. We don't do that. We get the universities to do it and there's a huge untapped layer before us if you will that will help deliver positive changes to social enterprises. That then helps grow your influence and your outreach if you will. Mark, one minute please. As we said we got the University of Osaka in Japan came to see us. We've had six different delegations from South Korea. We made a documentary for South Korean TV. We've had visitors from the US plus all over the UK and to go back to the questions I asked at the start so and the reason why I asked them is one where would you go for a coffee with your friends and most people would say Costa or Starbucks or some small independent but when we came when we launched Circle about 89 years ago that the offer to older people was a coffee in a community centre. Now we use community centres we still do and we'll continue to do but that was the only offer but absolutely most people don't pick that as the first choice so we had to to develop that offer. Why I say about the Sex Pistols. My youngest daughter is missing going to concerts she's 26 the 26 year old who attended the Sex Pistols concert is now 70 so the offer to older people has to change to reflect what an older person now is and the Cavern Club the 26 year old who attended the concert of the last concert at the Cavern Club of the Beatles is now in their 80s so I just wanted to show and define what an older person now is if you will so but we always come back to this does Circle make a difference and that's why any organisation does the work you do make a difference and that's why data is so important. So apologies for the glitch at the beginning but hopefully it's all worked out in the end. That's great thank you Mark and now we've gone to hear another case study this time from the Ethnic Health Forum so Haneesh is going to tell us about the work that they do there. Good morning afternoon actually hello hi Lucy can you hear me? Yes we can hear you. Okay I'm going to be pretty pretty pretty quick and short really the question for us was what was the what is it that we were not doing and I think about it two years ago we were delivering services without with minimal funding so we had few small pots of funding in a thousand pound here 2000 from the 500 so we were totalling alone but over that period we then started to say actually if we are to develop as an organisation we need to have some hard evidence so we kind of very crudely started to record stuff we were doing so lots of people were coming into the organisation and mainly started off with welfare rights advice so slowly but surely we and again we were always lack of staff very small organisation we're still only a four part-time staff team who provide welfare rights and other limited services often we were lucky we had a good volunteer as well who helped us to get the data together so anyway what the message that I really want to share is how a small organisation can actually use data information and as Lucy mentioned earlier data is gold dust I think for us putting that sentence or one by line is really the crux of it so I think we've learned over the years to make good use of the data information which actually helped us to build a good case for when we approached funders so we were our major funding came almost 18 months ago with the net community fund rotaries we had a number of meetings and actually we at our first meeting we presented the slides to the officers who visited us and basically they saw for themselves to say here is a small organisation doing really good work across the root level but they're struggling and really it was a simple request with them to say we've done this these are our statistics and we need some funding to employ paid staff who can actually deliver a consistent consistent service which meets relevant standards you know and the client or service user when he or she comes in gets the type of service that they deserve so this is a very crude slide but I think this was the beginning of our our journey and we have we have tweaked this now these were the initial categories you know employment support welfare benefits home office cases that we were either helping clients uh how many referrals from the NHS how many from the Manchester City Council related in terms of you know Manchester City Council or what was it you know we had families who needed filling in a form so that would go in education it wasn't the perfect way to monitor and evaluate but I think this was the start of it we have improved the way we collect data much efficiently now as we have developed uh next slide please Lucy if there is the other one okay this was an interesting one you know this clearly gave the the officers from the community fund and in fact we use this with other possible officers of the health services NHS local authority when we have a meeting we actually present real live data going back three months or six months and say these are the type of cases we are we are seeing and we have broken down so this was actually a slide going back almost 18 months ago we have actually now improved the way so these categories you see here which are maybe about 10-12 categories have actually have become more like 20 or plus categories so we are much better at breaking down exact reason why an individual has come to us or referred to us so again data is gold dust is how you use it to make a case you know to make a case for you know support to make your own case to say actually what you're doing is making a difference and part of this learning journey with our staff to make them help them understand why are we collecting often we had the questions we have now as well you know staff is saying why you want to this why you want to why you want to ask this why you want to collect this and I think if you I think once you once you help them understand the whole reason behind this they are much likely to actually be with you so again statistics very useful so all these things we were not being before and I think again the point is if you are a small organization capacity is an issue I think any small organization needs needs to have some kind of system or help or support to actually capture and reach the stage and one thing I've learned is you don't need to be a you don't need to have you know you don't need to be an expert you know sometimes stuff like this actually as you know a student who is in a he's or her final year of studies at the university can actually come up with this type of analysis you know once you've once you've given them the data information it's time consuming but they can actually help you prepare this so we've been lucky in that sense next slide please Lucy if there's a next slide okay this is again sorry there are only two slides okay if you just go back on the second slide that was on the that we were seeing have I got another minute Suzanne um yeah a minute okay so really the key message was you know is how a small organization like ourselves have have managed to make a case and we have actually convinced the funders so after having showing shown these slides plus others the lottery grant officers were very very very well impressed and said okay we would like some more further information but actually at the back of their mind they went away and said actually here's a small organization in the heart of the community we are in the M14 postcode covering Russia on Mosside Hume and you know there wasn't an organization providing a welfare rights advisory service you know we met the application and we got our basic first grant you know three-year grant which actually at the moment is covering you know salaries for three-part time staff and we have built on that actually so yeah I think presenting and you know collecting good information data makes a good business case as evidence for any organization who are growing and developing really um and we would we are always interested in partnership work with organizations across Greater Manchester I was very very glad I mean I heard Ma the previous speakers and the Rochner organization I think they have done a fantastic job I think I would we would like to aspire to get to that stage and we're good to touch base with them at some stage equally you know developing links and partnerships is a way forward so currently we're also involved in a number of interesting projects working with University of Oxford and Warwick on a quite a big national piece of work around developing an app to monitor data around mental health which is work in progress with I think the Bio University of Oxford Department of Biodata and Warwick's IT department so we are quite innovative in that sense as well we kind of think outside the box you know like to create new partnerships with local partners and you know partners across the country as well happy to answer any questions via email or you know I'm sure Suzanne and her team will share contact details later on so I much appreciated the opportunity given to us today to share our experience yes thanks Hanif and I think just now we're going to switch back to Lucy for just a bit of a sort of demonstration of how the data can be used yeah hi I'm just going to get my screen up and then we'll have a look at that let's go to Slycho Slycho okay so yeah so thanks for Mark and Hanif because they're underlining a lot of messages here so what I'm going to do with this presentation very quickly is to just give you an example of how some of the findings can really work for your service and show you stuff that perhaps you weren't predicting so they're making sense of data this is an example from an unnamed alcohol detoxification service in the northwest this service particularly prides itself on being able to reach out to socially excluded and they're more vulnerable people who are going to be using the service that's an important point that is their raison d'etre so what they wanted to do was to look at how their service was supporting people to complete an inpatient detoxification that is to to stay long enough to to have been detoxified safely okay so a little bit of background is that it's not going to the next one my screen doesn't seem to be going to the next slide for some reason this is weird ah okay okay so um what was introduced in the in the in an area in the northwest as a group of services that were providing detoxification for people who were alcohol dependent was a screening process so this was introduced to lots of services providing these these needs in this area so this service had to go over to a screening process so in other words people were screened before they gained admission to the service so we're calling this here um an open referral what they used to do people who used to be able to walk through the door and say I need to detox or um a screening process that they called gatekeeping so somebody would come into the service be interviewed and then either admitted or put on a waiting list or sent somewhere else so they collected data on who was using the service during open referral um process and during the gatekeeping process so they asked asked the man met to examine this database uh to have a look at client admissions so we also simply compared the data for direct admission people who could just get in through open referral to people who had been screened and then they also had records on who had completed treatment and had a planned discharge that is they were they were sober they were detoxed they were perhaps put in touch with continuing services compared to people who had an unplanned discharge that is they probably left they said oh I don't like this anymore I'm going home um probably to drink uh or had some other reason that they needed to be discharged possibly bringing alcohol into the premises and things like that so we compared the gate kept kept data and the open referral data against what type of discharge people had so very simple really simple um a two by two examination of the data we simply looked at that in terms of percentages so as this table can show it's a two by two table there's gate kept people who had a planned discharge and that was at 79 percent of gate kept people had a planned discharge against 20 percent who had an unplanned discharge they left all they were asked to leave and then we can compare that to the people who had an open referral as we can see only 72 percent 73 had a planned discharge against 27 percent who had an unplanned discharge so just by eyeballing this data it does look like that gate keeping improves the rate of planned discharge I'm hoping you'll all agree with that it's not a lot but there we had a lot of a big sample size so it was actually a significant difference so what we also looked at was those very valuable demographics we looked at people's age their unemployment status their gender their housing type their ethnicity and the SADQ is the substance abuse disability questionnaire of severity of alcohol disorder questionnaire I'm sorry but it basically shows how how much of a problem of alcohol is for these people and then we took one by one we looked at those and looked at successful discharge compared to unsuccessful or unplanned discharge the dropouts and asked to leave etc and lo and behold what we found was that there was a difference in demographics of who was having a planned discharge so just as an example here let's take gender for every one male as open in open referral 1.28 women were having a planned discharge compared with in the gate kept kept the ratio of men to women goes up for women so for every let's say 100 men there are now 162 women as compared to 128 women age gate keeping is as you can see they're valuing people over 45 so for every 100 people under 45 there are now 144 people having a successful discharge under the gate keeping system employment didn't it was was a bit of a strange one that went the other way but for housing again people with stable housing were being more successful with gate keeping than people with unstable housing so I think we've got a poll here I don't know if Lucy North can put it up or just to ask some questions on what you think this data tells us and you can tick on whatever things you think are true or what the evidence you think is telling us here a lot of people are agreeing with gate keeping improving the services efficiency so yeah this data would look good wouldn't it to a funder you'd be able to say look we're being really efficient with our gate keeping is gate keeping making the most of the services resources possibly because we're not wasting wasting resources on people who are going to drop out okay there's gate keeping making harder for those with most needs to access the service well if we can see here that there are some results okay so most people are tending to go with the efficiency absolutely okay so can we stop sharing the results now I think if we finished okay so in effect all those answers could be true it does just depend on which way you look at it and what you value for your service now remember at the beginning I said that this service it's sort of mission statement it raised on debtor was to reach out and help people who were the most needy the people with severe alcohol problems and people with unstable housing the people who found it difficult to get into services elsewhere so while we can say yes we've got we've got gate we've got gate keeping now we're being really efficient we can also show our funders the data on how that gate keeping is actually producing a barrier to people to access our service so what this service could advertise for its services and for more funding is perhaps that's if they get rid of the gate keeping which is exactly what they did they start they went back to open referral and started getting more of those people who were more vulnerable and they could demonstrate then that because we've got an open referral system we are able to help more people who are actually in greater need okay so I think I've got another slide here but which confirms that which is not working again but it basically summarizes that so just to finish then yeah that basic data you could see from these slides that we simply use the basic data that they were collecting as people came through the door we could have done a financial analysis as well which would have highlighted the the results but for this service it particularly focused on justifying what they were there for why they were different to statutory services for example okay I'm going to leave it there and we'll answer questions later


The researchers proved this by studying mice with breast, lung, and prostate cancer. When they removed the vertebral stem cells, the cancer spread to both the spine and long bones at similar rates. This suggests that MFGE8 and these specific stem cells play a crucial role in targeting the spine. This exciting discovery opens up a new frontier in cancer research. By understanding how MFGE8 attracts cancer cells, scientists can potentially develop new treatments to block this process and protect the spine. This could mean less pain, better mobility, and improved quality of life for cancer patients. You may want to consider Chicoil PEMF therapy, a non-invasive approach that's gaining traction for its potential to promote healing and general well-being. Give this a like and comment below. Share this video with anyone who's been touched by cancer. Together we can unlock the secrets of the spine and give hope to millions. Follow us for more.