 Guitar and Excel, Interval and Modes, Compliment and Parallel Worksheet Part Number 6. Get ready and don't fret, because it's just a board with strings on it, and Excel's going to show us how it works. 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 a prior presentation, so if you want to build it from a blank worksheet, you may want to begin back there. However, if you do have access to this workbook, there's a bunch of tabs down below, including the first two, representing the final product, the end worksheet that we're putting together here, the numbered tabs, tying into the related video presentations, so that you can follow along in a step-by-step process, the blue tab on the right, starting out where we ended off last time. So last time, we were doing some comparisons, so we had our worksheet on the left, and we were comparing it to our worksheet on the right. I'm going to delete some of the stuff that we created so we can do the next step of the building process, which will be to take the major's constructions and then copy it down so that we can look at the related modes. So I'm going to delete this stuff. I'm going to delete these red items. I'm going to remove the conditional formatting by selecting this area, home tab, style, conditional formatting, and clear the rules. Get out of here rules, clear rules, and then I'm going to unhide the cells that are in between here, so I'm going to select the whole thing from A on over to maybe BA so I can make sure I have everything selected to see all of the columns, right-click and unhide. So there's all of our columns. Just a quick recap of what we've done thus far. We created the musical alphabet. We then numbered it one through twelve plus around the horn to one again. We then put the numbers and letters together. We provided a key starting in this case in the key of C, but which can be adjusted, which will adjust, for example, if I go to 11G, the starting point, but we're going to keep it on C because that's the easiest scale to look at. But that will adjust the relative position and then we'll take a look at the distances between the relative position and every other note, which we can name the intervals. There's only eleven of them. We'll do that in half steps, the easy way, and then we'll do that. We thought about it in terms of the actual interval names. We abbreviated the names and then we made a symbol that shows both the units away from that starting point and an abbreviation or symbol of the name. We then used our construction format of whole, whole, half, whole, half, whole, whole, whole, half to make our scale in the key of C, seven out of twelve notes. We mapped out the scale and chord constructions from the key of C and then we also saw that in a circular format on the right and we did a similar worksheet mapping everything out in terms of intervals compared to the root note of the scale, which is the key of C. We made our fretboard on the right in terms of both the letters and the numbers, absolute numbers in this case, and then we looked at another fretboard that is in terms of the positions. So the intervals is what I'm trying to say here. Okay, so now we're going to copy this whole thing down. So remember if I look at this worksheet on the right, this interval worksheet is tying everything out to the key of C. We built everything from the key of C, but when I look at these related scale, these related chords that are built from the two note, so the two note in the key of C is D. If I build a chord from it, I just skip every other note, skipping from the D to the F to the A to the C and I could keep going to the E to the G to the B. What did I just construct by doing that? I basically constructed the mode. These are the notes in the mode of not D minor, although D minor will give you the same minor one-three-five of a minor chord, but D Dorian, right? So what I want to do is see this in terms of the D Dorian, which will give me all of this information tied not to the C, but to the D when I look at the relative positions, which is how we usually name the chord positions as being tied to the first note in the chord, which is often the lowest note in a normal construction as opposed to the first note in the scale that we constructed it from in this case. The key of C. All right, so we're going to copy this down. Now, before we do that, we have a little trick and I've learned something new since last time we did that. So anytime I have an absolute reference, it's going to mess us up. So notice in here, for example, I have these absolute references and these are basically tying me out to these columns over here. The problem is if I copy this entire worksheet down, I want these relative columns to copy down as well. So last time I actually went through here and removed these absolute references manually because they have already served their purpose, meaning I had to make an absolute reference on this cell so I can copy it through this whole thing and have those two references not move. But now that it's been copied through the whole thing, I could remove the absolute references in all of these cells and it wouldn't change anything because they've already served their purpose in the process of copying and pasting. But when I copy and paste the entire thing down, the absolute references will work against me. It'll be a problem. So I need to remove the dollar signs. So there's a fancy way to do that. It's pretty simple. I've surprised I hadn't thought of it last time. I think I might. In any case, I could select this whole thing and we could go to the home tab and we can go to the editing and the find. And I'm going to go into the find and I'm going to use the find and replace. And what I want to do is find all the dollar signs and replace them with nothing. So it'll remove all the dollar signs. So for example, all the dollar signs in there. They'll just be removed and it shouldn't change anything in our actual cell in our actual outputs. So let's go ahead and replace all. Let's check it out. All done. We made 1200 changes. Boom. Great. And now everything's the same. But now I don't have those absolute references. So when I copy this whole thing, it will then pull down this, the columns that it was pulling from over here. And it'll now put a reference to the related column. So really super handy tool that that I didn't do last time. So let's see any other absolute references we have here. So here's an absolute reference or so here's some absolute references, same kind of thing. These two columns are coming from here. I copied it down. I wanted to make them absolute. So these columns didn't shift down as I copied it down. But now that they have been copied across, I would want to remove them because when I copy the entire thing down, I don't want the absolute references because I want the relative references to move on the entire thing. Okay, so I'm going to take these then and do the same thing. I'm going to go in here and go to the home tab, editing, find and replace, find and replace the dollar signs, replace it with nothing, nothing. We don't need anything there. There's too much stuff. You have too much stuff. Get rid of the stuff. Stop making more stuff. Get rid of the stuff for crying out loud. Okay, so then let's see if we have any more here. This one has absolute references in it. So this one is doing a VLOOKUP same thing. It's looking it up over here. So those absolute references are used. So when I copied it down, the cell references moved down, we want to remove them. Let's select this whole thing. Let's do this whole thing right here and do the same thing. Let's do the whole thing so we can do the same thing. Home tab, edit, find it and replace it. Find the dollar signs and replace it with nothing, nothing. Just like I replaced all the wardrobe in my closet that I haven't worn my entire life. I just threw it out and replaced it with nothing. It's just taking space for crying out loud. Okay, so then if I go into here, same thing. So now these we used a mixed reference. So when I copied this down and then these are the absolute references. So once again, I don't need those when I copy the entire thing down. So I'm going to remove them now. Look at this is so much easier. It's incredible. Incredible home tab editing, find and replace and replace it with nothing. And it should not change anything. Everything is the same. Everything has changed, but yet it's all still the same man. Whoa. And then here's same thing here. Absolute references. I could have done this to the whole thing at one time, but whatever. Let's do this again. Home tab editing, find and replace to do to find and replace. Okay. Done. Okay. So now the absolute references has been removed. Okay. And so no more absolute reference. Now I can take this entire thing and just copy it down. So I'm going to copy it down and the whole thing should move down well because there's no. I'm going to make it a little smaller so I can see how far down I need to go. That's too small. Let's go down. I need to go down to like right there. So I'm going to copy from the one down to that right there. Copy it. Actually, I could have gone further. It doesn't matter if I go too far, but that's where I want to paste it. That's the point. I want to paste it right here. You got to make sure that you're pasting it all the way in column a all the way in column a. Okay. All the way in column a. Okay. Okay. So then we're going to say that this still looks like it's doing what we should what it should without those absolute references. So nothing looks totally wonky here. So that's good. And then over here what I want to do is change the key. So the key I don't want it to start from a four. I want to start it from the relative to so that I want to start it. So there's the four. If I look at my notes, let's look at it as a circle over here. Now we're going from the four to the one to the two. We're going to start it from the two. So I'm going to change it to that's going to be a six. So we need to change it to a six, which would be the second note. If I was looking at the seven note scale, it's the third note here. When I'm looking at the 12 note musical alphabet. Okay. So now the key has changed. I'm not going to make this green anymore because it's not something. The green is indicating that that's something I changed. And this is going to do automatic from here on out the cells underneath it. I think are populating properly. So now we're measuring everything from this. Maybe I'll make it like yellow just to show that that's the it's not. We're going to want to change it, but that's still like the changed factor in it. So it's now a D. So now we're measuring everything from the D. So notice our ruler didn't change. It's still how far out is it? It's either zero notes out or one note out or two notes out or three notes out or four notes out. But now it's comparing the ruler to a different starting point now starting on the D as opposed to the C. So now that the D itself would be positioned zero or the perfect first. The second note would now be a one or a minor second and so on and so forth. Now that populated over here as well for our musical alphabet. That's great. However, the formula here also needs to change. It's no longer whole, whole, half, whole, whole, half. That's the major formula. The minor formula is going to be equal to and I'm just going to start it after the six. So in other words, up here, if I started on a major formulas, whole, whole, half, whole, whole, half. But now if I start the same formula from this point, the two, I get to the same thing but starting from a different point. That's all the differences in the mode. So if I start from this spot, I'm going to delete everything underneath it for now just so we can see what's going on. And I'm going to copy that down until we get back to the six. So I copied it down till I get back to the six. So now it's a whole, half, whole, whole, whole, half, whole gets us back to the six. It will then repeat. So I'm going to go down here to repeat it. I'm just going to say this equals the two up top. And then when I copy that down, it will pull down the next one down and that'll just repeat it. It'll just kind of run run down caterpillar down down there. So now you can see it's the same because if I started on a C, it's still this is my double check. It's still whole, whole, half, whole, whole, half. If I started on the C, right? But if I start on the six, then it's whole, half, whole, whole, half, whole to get back to the six. And I think that's all we need to do that will then populate the changes on the right hand side, which should populate properly. Now I need to change the name. So I'm going to call this a Dorian. I'm going to say this is the Dorian. And I'm trying to say it's the two note from the major scale, just so we know it's the relative two from the major. And on the right hand side, we have our circle now. Now the one is the D, right, instead of up top where it was the C. The one here is the D as opposed to up top where it was the C. And when we look at our relative references down below, now this first note is is a perfect first for that D. Now this D chord construction that's going to be created is going to be a minor chord construction. So we have a little bit of an issue with then like how do I this should still be a one, but it should be a lower case. So I'm going to I'm going to delete these and this gets a little fancy, we're going to get a little fancy here. This gets a little intimidating at first. So what I'd like to do is I'd like to pull the notes from up here. It's going to be the same like the C, the F and the G are going to be major chord constructions and the two, the D, the E and the A are going to be minor. But I need the D to be now one instead of a two. So it needs to be a minor two or a lower case two. How can I do that? Well, here's here's one way we could do that, which is kind of long, but it's good practice for our formula. So we could say if brackets, here's our logic test. Now I'm also going to embed an or because I'm going to have multiple conditions. So I'm going to say if or because I have multiple conditions. So if anyone if if any of these conditions are met, meaning what is the condition I want this to this one. You can't see it, but it's that one right there that was equivalent to that one. If that one is equal to the C or that's first condition or comma. That's why it's an or this condition. If this is met this cell again that it's a one. You can't see it, but there's a one right there. If that is equal to a a hold on a second. Did I pick up the one? It's not the one. Let me redo that or condition is going to be this cell. So this cell up top, which is the D, which you can't see right there. If that is equal to either a C up top, because if it was equal to a C, it would be a major or comma. I'm going to the same D again. You can't see it, but it's right there. It's that D. If that is equal to then the F or comma. If that D again, which you can't see is equal to the G. If it's equal to any of those, then so I'm going to close up the or function and then say comma. So now I'm on the next test. If any of those are are met, then we want you to make it a Roman numeral tab of that one. So now I'm looking at the one right above it equivalent to this one right here. I want you to make it that Roman numeral one. Now none of those conditions are met because this is a D and those are CF and G. So what happens if it's not met? Well, if it's not met comma, then what do you want us to do if it's not met? If it's not met, then I want you to take a lower case Roman of that number. So that means it's going to be the lower case number. Okay. Wow. That's fancy. Let's see. It did it. So there it did it right. Boom. And then if I copy that now, I can't copy it down because I have to use some absolute references here. So now it's going to be that that we said or if any of that equals that. So, so, so if this equals a so the AS is need to be absolute. So I'm going to say F4 here, F4 here, F4 here. And so that should be good. I so I'm going to say okay. And there it is. Let's copy it down and see if it makes sense. So now we've got the the majors are the G because it's uppercase the F and the C GFC which happened in this case to be the three four and the seven and the minors are going to be the D the E the A and the B. Wait a second. The B should be diminished. So now we have this little dot. So there's one other kind of a wrinkle in here. So let's so how can I do that it's going to get more complicated. Okay, let's do okay. So what I'm going to do is I'm going to go in here and say, okay, well, if let's delete the end of this and say that if it's not met. If it's not met, what do you want us to do? Well, I'm going to embed another if because there's another branch. So if it is met, make it uppercase. If it's not met, then it's either going to be lower case without a dot or lower case with a dot. So I'm going to put another if in here and say it's going to be lower case. What do you want to do? Then I'm going to say, well, if this cell again, if that cell is equal to a B. F4, let's make it F4 absolute F4. Then so let's if that's equal to a B, then what do we want you to do? Then I want you to take this number that number. No, I want you to take a lower case lower lower Roman of this number that number one lower Roman number one. Close that up and I want to add a dot to it. So I put an and so I'm going to say and and add a text, which I need to put a quote, period quote. That's what I want you to do. If it's a B, what if it's not the B comma, if it's not the B, then I just want you to do the normal lower Roman of that number one. Oh my goodness, are you kidding me? And it didn't like it. Okay. I think what happened is I didn't close out this argument right here. So this Roman included up to there and I think I need another bracket there to close out that argument. Actually, I need it out. I need it out here to close out that argument. And then I'm going to go to the value if false, which is the lower Roman, it's going to add some brackets on the end. And so let's say I think that did it. So there it is. It added more of the brackets on the end, but let's copy it down and it should put the dot where we wanted to put the dot. So so so there's the B has the dot. Now you don't really have to do that. You can kind of figure it out yourself and plug that in there. But if you wanted to copy and paste it, you could do that. And that's our fanciest and longest formula of their logic formulas in our process here. So there it is. So then we can say down here it picked up the same one. So that looks right. So I think that is everything. And I think we can repeat this again. So I think that's everything. So let's try it again. And I'm going to copy this whole thing down. Now I'm not going to remove those absolute references that we just made in here. Because we actually want the other ones that we do down below to have a similar comparison to the major. So those absolute references we actually want as we copy it down. So now we're going to go to the Phrygian. Let's do the same thing. Ultra base. Another time. If you please copying from here to here and pasting it down there. Boom. And so I think this looks good movie B to the end B and how you being. And then this is going to be equal to the eight, which I think is actually correct here, but let's just check it out. So now we were on the D. So now we're on the E, which is the eight. So that was correct. So it actually moved it down properly. So that's going to be an eight. Let's pick it up from right there. So there's our eight. So now that's our new relative position. So we're measuring everything now from the E. So E now is relative position zero or a perfect first. And then we need to adjust our our formula. So let's actually remove this formula first this time. I'll just delete the formula. And I'm going to say instead of whole, whole half and so on, we're going to go up top and say now we're starting from the eight. So the one after the eight is that half step enter. And then I'm going to copy it down till I get back to an eight, which is right there. And then everything below that I'm just going to repeat by saying this equals the one above it and copy it down, copy it down. And we can double check that it's done correctly by looking at the C and saying is my formula correct from there, which is whole step, whole step, half, whole, whole, half. It looks good. So I'm going to say, all right, and then I just need to change the name to the Phrygian to the fridge. It's the fridge. The Phrygian. So there we have that. And now the relative positions are down here comparing to the E. We have our circle that looks good. We don't need to redo the Roman numerals because we can still see that the C the D. I'm sorry, the C, the G and the F are the uppers. The B is still the dot diminished one because our super fancy formula has pulled over properly. So that is nice. Look how much time it saved us. It might have taken us like a month to learn that formula, but now it's paying off. It's paying the dividends right now. That's what it's doing. Let's do it again. Let's let's go ahead and copy the next one. And we'll say copy this down here. And then paste that down here and do it ultra base. Another time, another time. All right, let's do it. This one's now going to be the we're going to the last one was Phrygian. This is Lydian, I believe. Is that the proper way of saying it? So we were over here. So now we're going to the F, which should be a nine. So we'll say that's the nine. That's our key starting at the nine. Everything is relative to the F now. F is now zero or perfect first. Everything's being compared to that relative position. And our numbers here need to start at the formula, not a whole whole half. I'm going all the way up to the top to relative major. We're going to start at after the F. The whole right there. And then we'll copy it down. Let's delete the stuff under it so it doesn't get confusing. And we'll copy that down until we get back to an F. Boom. Happens right there back to an F. And then we'll just repeat this equals the one above it. And we'll just repeat it down. Down, down. Okay. So I think that's right. Double checking. If I look at a C, we get the whole step, whole half, whole, whole, whole half looks correct. Correct. And then we'll call the name of this, the Lydian. So I'm just going to Lydian. That's a Lydian starts on the F. F is the perfect first. All right. Let's do it again. Next, we have the mix of Lydian mixing it up with the mix of Lydian. All right, let's do that. Thought Lydian Lydian was too plain. We've got to mix it up. We've got to mix it up with the mix of Lydian. If you mix up the Lydian, it's way crazier. So we're going to go in and mix that Lydian up. And then we're going to go to so this starting point is no longer an 11. So we went from, if I look at the circle, we went from here and F. Now we're going to go to the G, right? So it's a G is the 11. Now we're on the 11. So now is the 11. I'll just pick that 11 up right there. And then everything is starting from the relative position of the G, which is now perfect first. The ruler doesn't change. The starting point does. That's what we're doing here. And then I'm going to delete our whole, whole half or whatever formula to start at that relative position. So if we start the whole, whole half formula up top at the note after the G or the step after the G, we take a whole step right there in our hopscotch. That's when we do the hop and we skip a note for a whole step. And then we're going to copy that down till we get back to a G right there. And then it's going to repeat equals the one up top. And then we'll copy that down. We could just double click on it, by the way, and double check going to the C. Do I still have my formula starting on the C after the C whole, whole half, whole, whole half back to the C. Looks like it's correct to me. Let's then label this mixolydian. We took the Lydian, we mixed it up and we called it the mixolydian. That's what we did. Okay, let's do it. Another one. So another one, ultra vase, which we'll call the good old minor, or you might call it if you're fancy. Aeolian. Aeolian. So we're going to go from here down to here. And paste that there. I'll scroll in. And so now we're going to change this key here. It did it for us automatically. But let's just double check if I go back up top and look at my circle that's starting at the key of C. And we were on a G. Now we're on the six, which is an A. And if I go back on over here, we're on the A, which is a one now. Boom. So we've got the one. And then the ruler stays the same. This is P1. We changed the relative positions over here. The relative positions need to change. This is going to be equal to scrolling up. And we're going to say this is equal to after the one is the two. So the one right after the A that we're looking at. I'm going to copy that down till we repeat back to the one again. Delete these and then repeat it equals that to double click, copying it down. If I look after the C, there's the four whole step, whole step, half step, whole step, whole step, whole step, half step. So I think that's correct. Hopefully let's copy the minor over here. There's our minor. All right. And then so that looks good, I think. And then we can see that the B is still the diminished here and our minor, which is the two now. And we're starting on the one now being the A, which was the six before. Let's do one more. We're going to say the Locian, the crazy one. This is the Locolocan. Let's do this one. Can't forget about keep your eye on that one. Because it's crazy. We're going to copy this one and paste it down here and then we're scrolling in. And this one's going to be the B. It's already there. But let's double check it going up top again and scrolling to the right. We left off on the A. Now we're going to the B, which is the seven of the related major, which is a three note or a B. So we'll pick up the three, which is a B. And so now we're relative position three or B is now the zero or perfect first. The ruler stays the same. The starting point, that's what changed in our worksheet here. Let's delete the intervals and do the intervals from the B position. So I'm just going to go back up to our starting point up top and change it after the B. So there's the B. There's a whole step. I mean, a half step is the next one down. I'm going to copy that until it repeats back to the B right there. Delete this. And then we're going to say this equals the one so we can repeat it down again. Double click it down. Double check like at the C. There's our whole, whole half, whole, whole, whole half back to the C. So that looks correct. Let's change the name of the Locian. There it is. And the Locian's the weird one in part because of course it starts off on that funny construction of the diminished chord at first, which has the minor third, by the way, that's why it's lower case, but it has a flat five, which is strange. So you can see right here the fifth is different than the fifth on all the other ones, which is a perfect fifth. You got a flat fifth, a six note away versus a seven note away on all of the other ones. It's all the same on all the other ones. That's why Locrian, that's what makes Locrian crazy. We found the gene, the core gene that turned Locian loco. Okay. So now you can do comparison. Now we can, we have, I think that's basically it here. So now we can basically use these worksheets out to do comparisons, right? And so this, we can, we can map, if we map out the whole, the whole scale, the scale notes will be the same. However, when we, when we look at the intervals down here, the names of the intervals will change depending on what the root is. And that's why it's useful to look at the different modes, because now I can map out this whole fretboard and look at the intervals. When I change the chords, I can look at the related intervals to the changed, to the changed interval worksheet. That's the idea. All right. In future presentations, we'll, we'll do the, the parallel stuffs, which will copy this worksheet over and then we'll do the parallels, which means instead of changing why this is useful, by the way, is one, you can, you can then look at each of the chords that you're playing in the key of C. You can see which chords you need to play by just using this little, this little kind of cheat tool of saying, I'm going to play a major, major one, four, five, minor two, three and six. And then the diminishes to seventh. So you, so you can use that, but you can get into more detail, especially when you get into details after the one three five, when you start adding sevens, nines, elevens and thirteens, nines being the equivalent of twos, elevens being the equivalent of fours, thirteens the equivalent of sixes. Then it's useful to, to map out its related mode. And so remember this Dorian, this notes right here are basically just the same as mapping out the Dorian mode, which you can think of as a minor mode, because the one note that you would construct in Dorian would still be a minor chord as it would be if you had the related minor scale, but we're really tying it to the notes that are in the C major, right? And the actual minor that's related to the, to the C major is A minor. And then again, these are basically the A minor. So you can think of all these notes that we created when we start building the chords from it, when we start mapping out the chords, you can, and you start to re-align the intervals in relation to the one notes of all the notes in the major chords, you're basically just mapping out all of the modes, right? So then we can map out the modes down here so that we can see the intervals related to the modes. Now the next thing we would want to do is to say let's keep the C the same and map out the parallel modes so that I can see the Dorian, the Phrygian and so on, the Mixoladian, the Lydian in terms of keeping my root note the same. In that case, when I map out all the notes on the fretboard, the notes on the fretboard will change, but they will all, but all the positions will be relative to the root, which will always be the same. In this case, we're looking at a C, so all the C's would be P0. Okay, so we'll look at that next time, or start at least.