 Guitar and Excel, Interval and Modes, Compliment and Parallel, Worksheet Part Number 5. You know, Phil keeps on telling me I don't have the right to teach music because of a lack of a music theory, PhD or whatever. Telling me how some doctor so-and-so over there is totally better than me because he really knows his crap. It's like whatever, Phil. As for me, it's true. I don't really take the time to get to know my crap. I mean, to be honest, as soon as I see my crap, I just flush it down the toilet. So no, I don't know it very well. Like, right when my crap says hi, I say goodbye with a roaring va-woosh. The toilets today aren't worthy of the name. They come in designer colors, and they're too low. When you flush them, they make this little weak, almost apologetic sound. Not the first. And when you flush it, ba-woosh. You know, I think the real question here is not how well doctor so-and-so really knows his crap, but rather why doctor so-and-so really knows his crap. I mean, it's kind of strange. I would think that somebody who really knows their crap would be a doctor of like nutrition or something, rather than like a music doctor. Anyways, whatever. I don't even care because it's now time to cut the crap so we can get to learning the guitar. And if you want to really get to know your crap, you could do it on your own time. By the way, I have learned a lot from highly musically educated people providing free content, which I'm very grateful for. I'm happy to listen to anybody as long as they're providing useful information. I'm just kidding here. So in any case, let's get to it. Here we are in Excel. If you don't have access to this workbook, that's okay because we basically built this from a blank worksheet, but started in prior presentation. So if you want to build this from a blank worksheet, you may want to begin back there. However, if you do have access to this workbook, currently seven tabs down below, the first two tabs representing the end product, the final worksheet that we will be putting together, the numbered tabs tying into the numbers of the video presentations where we work that part of the practice problem, that part of the worksheet. The blue tab on the right is going to start where we ended off last time. And let's give a recap of what we have done thus far. We started by creating the musical alphabet. So we have the musical alphabet here. And then we numbered the notes in the musical alphabet from one to 12. And then we went around the hoard with another one down here. We combined the numbers and letters together because I'm going to argue here that that's useful to learn. And then we created a key which will allow us to sort our musical alphabet by whatever's going to be that starting point, in this case a four, which is a C. So now we're pulling in our musical alphabet but starting on a C. Then we looked at our intervals, the easy way, just noting that there's 12 intervals, including the point that you are on, and just numbered them, measuring them from this point that we're changing on the green cell. And then we discussed what the interval names are. Here's the full names of the intervals. And then here's the symbols. And then this is the distance from the point we're starting at, which in this case is the key of C. And then the interval name, a perfect first, a minor second, a major second, and so on with the two representing how far it is away in notes or half steps from this green cell, our starting measuring point. So then we created our worksheet over here that's going to give us these seven notes in whatever scale that we're focused in on, in this case in the key of C, the scale being determined by that green cell once again. And so now these are going to be the seven notes out of the 12 notes. We got here by applying the musical formula, starting with a C or a four, and going up two steps to get to the D, up two steps to an eight or an E, up one step to get to a nine or an F, up two steps to get to an 11 or a G, up two steps, 12, back to one, which is an A, two steps to a three, which is a B, one step to back to the four, which is a C. And then we just repeated that a couple times down here. So then we populated our worksheet with these. We numbered the relative positions. These are relative positions to the scale now. And then we gave them Roman numerals as well, which gives us an added level of information provided by the ability to have uppercase and lowercase, uppercase meaning that we're going to have a major chord construction versus a minor chord construction. And then we basically constructed the chords from each of these notes in the scale. And we do that by selecting every other note in the musical alphabet, which we discussed a little bit when we built this, but I'll try to make that more clear now with the next part of the project, which will be creating a circular representation of that on the right hand side. And then down below, what we have here are the interval representations, perfect first or one note away from the starting point in this case of a C. And then this works out quite well when we're mapping out the C chord. But when we get to mapping out the D, it gets a little bit confusing because all of these intervals are in relation to the key of C. So you can see it that way, but oftentimes when we move to the D here, even though we constructed it from the C major, we're actually going to talk about the intervals as though it's coming from the D as the relative starting point. So we moved the measuring tool. So hopefully that will become more clear now as we do the next part of the project. I'm going to put this into a circular format on the right, and then we'll get a better idea of how we got to these, and then we'll do the same thing with the intervals over here. So this is a little bit wonky to do in Excel because it's not as easy to make circles in Excel. So let me see if we can pick this up. So we're going to say one, two, three, four, five. I'm going to do it over here. So I'm going to say that the top of the circle is going to be this C. So that's going to be the top of the circle. And then I'm just going to go around the circle. So I'm going to go try to make a circle here. One, two over one, two down, let's do. And this is going to be the D. And then I'm going to go another one down. So I'm in cell B, F, six. And this is going to be equal to the E. And then I'm going to go down two cells and over one cell to get to the F. And then I'm going to go over two cells to get to the G. And then I'm going to go from the G over one cell up two cells to get to the A. And then I'm going to go up from the A to cells to get to the B. So we have somewhat of a circle in Excel. Let's make the cells a little bit smaller. So I'm going to highlight from B, A over here. And then I'm going to go to the B, F. I'll make this a little bit thinner. So let's see right about there. Probably be good. Let's make a skinny cell over here from the AZ. So we'll just have a little bit of space. OK, so then I'm going to label this with the Roman numerals now, which will give me both the relative position relative to the C scale. And it'll give us the construction of the quality of the note that we'll build from it, which in this case is a major given by the fact that it's an uppercase Roman numeral. So I'm just copying the same information over here. So here's the Roman numeral two. Here's the Roman numeral three. And then here's the Roman numeral four. It's a little bit tricky to learn these Roman numerals if you're not used to using them. I mean, most people have seen them, but you probably haven't spent a lot of time if you haven't worked with music or other things that use Roman numerals to look at them a lot. So, but once you get that, then it'll give you more information with the number. So we don't have to repeat the number. If you can see the Roman numeral as a number and have that added dimension of uppercase and lowercase. So there we have that. Let's, let's do some, let's center this. I'll select all of this and then go home tab. Alignment and center. I made this cell wide again. So I'm going to select all of these. So I make them the same width and then just change that width a little bit. All right. So then these ones, let's make these ones black and white. So I'll put my cursor in there. I'm holding down control, by the way, and selecting all of these cells at the same time, non-adjacent cells that are not next to each other. In other words, home tab font group will make this black and then the lettering, let's make that white. And then around this side, let's just put some borders around these. I'm holding down control and just selecting these and font group and let's put some borders around those. So now we have a circular format. So the idea then being when we construct, when we construct a chord, all we do is we take all of our notes and we select every other note that is in the scale, remembering that the scale only has seven out of 12 total notes. So if I go from the C and I skip the D, I get to an E. And if I go from the E and I skip the F to the G, that's the four, I mean the C, E and G, which we would call the one, three, five. Now I could keep on going to get to the seven from the G to the B, there's the seven, and then I could keep going. What happens if I keep going? Because there's only seven notes, right? That seven is equivalent to that B. It's the last note in our scale. So how could I keep going? Well, if I went around in a circle, you can see you can easily just keep going and you'll pick up the one that just skipped last time, which in this case would be the D, which we would call the two, because it's the two note of the scale. But when we look at the construction of the chords, oftentimes we want to put the most important ones up front. So if I'm trying to say the most important ones are the triad, the one, three and the five, then I don't want to go back and put the two between the one and the three. That's my justification as to why I think this is happening because it's a little confusing. But so then you keep counting up to go to the nine. So even though there's only seven notes in the scale, we have a nine, which is equivalent to the two, right? So now when we think about these intervals down here, note that you have to keep in mind that when you see like a nine, it's going to be equivalent to the two. So then I'm going to skip another one and that goes to the 11, right? So that's going to be an F and the 11 then would be equivalent to the one we skipped in here between the three and the five or the four. We skipped the four. And then we're going to go to the next one over and we went to the A and the A would be the 13 and the 13 would be equivalent to the six, right? So just so those equivalents, you just want to keep in mind on how that works is the general idea. Okay, that being said, we can also do a circle of thirds. Now note that I could do that from any point here. I could do that from the D. So here's the D. I skip every note. Boom, boom. And so now we've got the D. We've got the F and the A. I could keep on going. I could go to the C, right? There's the C. I can go to the E and so on. And I'm constructing all the notes within here. Now what do we mean when we say it's major or minor? These indications are telling us that at least the triads, when we do this construction, what happens is the intervals on the first three notes, the triads, will have a common characteristic which will be differentiated only by the third. The third will be major or minor. So we're defining if it's major or minor on the first three notes that we do this process of selecting every other note to look at the interval mainly on the third to see if it's a four note distance major or a three note distance of minor. So you can see here eight minus four is a fourth note distance. Nine minus six is a three note distance. So that's interesting. Now if I keep going out, it gets a little bit less clear when I'm looking at something outside of the one, three, five. If I go to the seven, the nine, the 11, and the 13, then it will depend, it'll depend more on whether it's the two note or whatever and so on. So in other words, when I look at this D, also realize that when I build it from this scale, I'm building it starting on the two. So you would think if I was to name it, it would start on the two and then the four and then the six, right? But that's not what we do. We call it the one, three, five of the two chord of the C major scale, the one, three, five not tying into the C, but it's tying into another relative position. It's tying into the D and more, you could call it the D minor because it will create a minor chord if you just look at the first three notes. But if you extend this beyond what we built here from the C major, you're actually looking at the D Dorian. So it would be the related mode. So basically this is just what we've built right here is basically just the related Dorian mode. If I look at the six, what we've built right here is basically the Aeolian or minor mode. So that's kind of the idea. So if I copy the same thing, let's show that down here. If I copy this whole thing and I put that down here, now it's just going to paste all the relative positions, but now it's pulling in the same information from an interval standpoint. So now I can see, okay, how is this first major? If I'm looking at a major, then the one chord is constructed by a perfect first or a one, and then we skip the two, and then we go to the major third. Now the major third, although we only skipped one note, is actually four total notes away, right? It's four total notes away because these notes only represent seven out of 12 notes in the musical alphabet, right? So it's actually four notes, and then if I go from here to here, then we skipped another one. That's going to be the fifth. When we say it's the fifth, we call it a perfect fifth, but it's actually seven notes away from the starting point, which in this case is the C. Now note down here, what we do not have is a perfect first in our graph here, right? Notice that this whole thing is represented relative positions to the key of C. So you have to recognize that. Then if I want to measure this from the D, we can look at the relative mode that we will create later, which will give us the relative position starting at the D as the starting point. Okay, so we'll talk about that in a second. We can also do a circle of thirds, which sometimes is a little bit faster to use. So let's skip a cell and do that. We're going to say this is going to be the one, and we're going to say this is a C, and then I'm going to go down one and over one, and then I'm going to skip the D, and I'm just going to go to the E. So I'm skipping every other one, just like we do when we do our chord construction. Well, let's do that after, and I'm going to go to the G now. So I went from an E skipping the F to the G, and then I'm going to go one over here, and I went from the G. I went from the G skipping the F. I did the G. Now I'm skipping the A to the B. All right, and then I'm going to go from here, and I'm going to say I kind of squished this together. I should have left more room. Let me pull this over. I'm going to pull this over a little bit. Let's pull this over like two for now, and I'll make this a little smaller. Okay, so then we're going to go from the B. I was on the B, and now I'm going to skip the C and go to the D, and then I'm going to go up here, and I'm going to skip the D. So I've gone around one time. I'm going to go around again, skipping the E and going to the F right there, and then I'm going to go up here, and we're going to say we're going to skip from the G to the A. So I think that looks about right, except I need to pull this like, I'm going to pull this into the middle, and there we go. I think that's right. So we've got C, E, G, B, D, F, A. Okay, I think that's right. I don't want to tell you the wrong thing. So we're going to then, let's make this small again, and now let's put the labels on top of it. So the E is the three, and then the G is the five, and the B is the seven, the D is the two, the F is the six, the A, A. Wait, that was the four, so and then the six. Okay, let's add one more column here. I'm going to put my cursor up top, right click and insert a column, so it's not right next to it. I'm going to select these and make them a little smaller, and then let's center this. I'm going to go to the home tab, a home tab alignment and center, and then I'm going to go, I'm holding down control and selecting all of these cells. I'm going to make them home tab, font group, black, white, and then I'll select these cells up top, and we'll go once again, home tab, font group, black, oh no, let's not do it black-white, let's do just borders. Okay, so now you can see it a little bit faster, right, so the C, if I just take the C, the E and the G, there's the C, E, G. If I start on the E, E, G, B, if I start on the E, E, G, B, and we've created it right there, right, if I start on the G, it would be G, B, D, G, B, D, and if I start on the B, it would be B, D, F, B, D, F, and if I start on the D, I went back to the two, right, which I can call the two, or you can call it like the nine, right? So I got D, six, D, F, A, so there's a D, F, A. So sometimes that's a little bit easier to see, let's copy that and put it down here so I can see the same thing in intervals. So now we just skipped every other one from an interval perspective. So there we have that. Okay, so now let's try to outline our fretboard on the right side now. I'm just going to create another fretboard, but this time I want to populate it with intervals instead of the notes. So I'm going to start this by just doing, by just copying the outline, and I'll do that by saying this is going to be equal to that zero, and then I'm just going to copy this across. I'm just going to copy that across so we can do that, and then I'll copy the formatting. I'll select this whole thing and format paint it, and I'll just put that right there, format paint, boom. And then we have a lookup. So what I'd like to do is find each of these letters up top and I'll look them up over here in our list. I'll look each of those up. Where do I want to look it up? I want to look them up here, and then I want to return this value here. So if I'm looking up an E, I'm going to say find that one of the E, and then return me the interval, which is a four note away major third. Okay, how can we do that? That's the trustee vlookup. So we're going to say this is not vlookup, xlookup, that's old. That's old news. You're a dinosaur. Get to the new one, xlookup. That's what we do now. So it's the xlookup. So here it is. We're going to say, okay, so what are we looking up? We're looking up this E8 and then comma, and then we got the lookup array. So I'm going to find the lookup array and we're going to say that's going to be over here. Control shift down. So we want to find it there and then comma and then the return. What do we want to return? We want to return this one, which will give it to us in intervals. Boom. Okay, there it is. So that looks right because that E in relation, yeah, is a major third, because if we look at our worksheet over here, right, the E is the third, which I can see here, major third, okay. Perfecto, just like Mundo would do it, my friend Mundo, and he's a perfectionist. It's perfecto just like Mundo would do it. Okay, so then if I copy this to, now what I want to do though is make an absolute reference between the arrays. So I'm going to say F4, F4, F4, F4, and then not F4 on the first one, because I want this one to move both to the right and down. Okay, boom. And then we'll copy it down to, so one, two, three, four, five, six, and then we'll take this whole thing, copy it to the right, copy that Roger, Roger down, Roger out. Roger is out. And then I'll select this whole thing and make it a little wider, because see, this is why I didn't want that dash there, because see, this gets a little wide, because see, this one's kind of fat. If I remove the dash, it'd be thinner, but I think people, the dash is helpful for people to see, so I'm not going to do it. I'm going to keep the dash, I'm going to keep the dash. Okay, so then let's select this whole thing and just format paint it. I'm going to go home font group, format paint. Boom, format has been painted. So now we have this whole thing, mapped out in interval now. And this, this, so what we can do now is I can put this interval worksheet next to my information on the right, right? So I can say, what if I just want to look to like, the fret five, and I want to hide, let's hide to here first. I'm going to say hide this information, and then I can look in here and say, okay, what do I want to keep from here? Maybe I, maybe I hide, maybe I don't need this. I'm going to hide that, and then I'm going to hide from here to here and maybe all the way to here and hide. So now we have, now we have our worksheets right next to, right next to each other. And I can see the interval worksheet. Let's go like that big. I'm sorry, I'm zooming in and out like crazy. And then we can, and then we can use our format painter to basically, to select certain areas within here if we so choose. So we can do things like selecting this and going home tab, format painter. And let's say we want to make this equal to the C and I'll make that one green because that's my one. And then I'm going to say I want this equal to the the E and we'll make that one red. Let's just keep it red. And then I'm going to say this was going to be equal to the G and I'll make that one yellow. And so then we can see those mapped out. And then if I put our squares on this, I can make a square here and say I want to make the shape fill outline and red. And then maybe a little thicker. Should I make it a little thicker than that? Okay, so then we can kind of see, okay, there's the C and you know that we're constructing that we would put our fingers on just like we saw in our prior worksheet. And I can create my related interval information down below. I could I could use do it with colors like I did here or I can just do it this way and say, okay, if I make that shape down here, there's my intervals. There's the zero distance away, perfect first. There's the major third, another perfect first. And you can start to see the intervals a little bit more clearly here, even though up top I basically colored the intervals, which we looked at in terms of just the one three five. But this will give you the more formal names of the interval. Now again, it gets a little bit it's a little bit easier when you do the first one, because if I go to the second one, this is where the problem comes in. If I if I manage the rules, and I want to look at the D now. So now I'm going to go, okay, where are my rules? There's no rules happening here. It's anarchy. We're going to say, let's go into here and say, if I change the green to be to be this one. And then okay, and then the red, I'm going to change it to be this one. Okay, and then the yellow, I'm going to change it to be this one. Okay, and then okay. So now I've got like a D minor. So if I look at that shape, boom, boom, boom, boom. I'm going to delete this one. And then delete this down here. Just copy this shape, holding down control. Copy paste and just move it down. So now you can see the kind of issue here because you would think this D would be the one. But it's not when I measure it in relation to the C scale, which is what we constructed it from, because we constructed it from basically the two note of the C scale. So it resulted in the same, what we would call a D minor, because the one three fives have the same distances between each other. But those distances are relative to the new central point, which we're thinking of as D, not relative to the scale we created it from, which is C. So that's where like the issue comes in. So it's kind of interesting to look at it this way, because then you can see how what the distances are from what we created it from, the C, right? But what we will do is we'll create the modes now underneath of this, and the modes will give us the proper measures in relation to it as the starting point, its relative position being the starting point. So the D is basically the Dorian mode, right? The the the six is basically going to be the minor mode. So we'll create and so this is also you'll also kind of get an idea of the idea of the fact that we can kind of think of minor modes and major modes, right? Because the D, you know, these of the modes that's coming off the two, the three and the six are going to construct a minor one chord, right? We know that's the case. And we know that the modes that are going to be constructed from the one four five of the relative major, the relative modes that are constructed from that, one being the major, right? But the four and the five are going to have a major one chord, right? We know that is the case. So we'll start to build that in future presentations.