 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number nine Get ready because it's time for our guitar skills to Excel 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 we started in a prior presentation So if you're using a blank worksheet, you may want to begin back there However, if you do have access to this workbook We got a whole bunch of tabs down below at this point including the example tab the finished work the end product in essence The answer key a bunch of starting point tabs tying into the video presentations as we work through the long practice problem the blank tab Representing the blank worksheet we started with and the one that we're going to be continuing with at this time Let's give a quick recap of what we have done thus far We started out listing the musical alphabet But we put it in a column type of format and we noticed that it's difficult to kind of sing along With the alphabet when you add the sharps and flats because you're like a b c what no a a sharp b C C sharp d d sharp e F F sharp and I notice we have an error here. It should be I'm going to represent the sharp in a flat like that F sharp G G sharp and then back to a it's hard to sing along with that and it's even more difficult to say it backwards, right? Because then you're like, okay g sharp or wait that should be like an a flat maybe because I'm going the other way G g flat F e E Flat see it's difficult. That's hard to do and it's it's especially hard to jump from here like a C to here and Try to say well, how far what's the actual distance between those two things? Musically that's kind of difficult to do because you can't use math to do that So we numbered it and said what if we just say one two three four five six seven eight nine ten eleven twelve Twelve eleven ten nine eight seven six five four three two one we can say that Backwards forwards we can subtract the numbers if I want to know the musical distance Absolute distance not relative distance between an F and a B if I know an F as a nine and a B as a three I could do math and then I could you know math is useful. So Then so we numbered it and then we put the number and the letter together And then we made our fretboard out of that which again, we did one with just the numbers These are the absolute numbers representing the notes, which is a lot cleaner to look at We did one down here This is the one we're going to use most of the time But if we were to see the the notes as numbers, this would be a lot easier and kind of cleaner To look at if we got really good at being able to convert back and forth between the numbers and the letters It looks kind of like that game that you have to you know You have that grid of numbers and you're trying to add everything up to nine. I think it's like called a Soka a Soka that's it. No, it's a Jedi. That's it. That's a female Star Wars Jedi. I don't know but I'm sure people know what that game is because I've played it It's good. So then then we went over here and we built our musical or our Scale so we started with the C scale because that's common We used our formula, which is whole whole half whole whole whole half In order to then map out and if we used our numbers again, the whole whole halves are just Representing distance so we can see that as two two one two two two one And then we just do a running balance and say well if I'm on note four Which is a C two notes up as a six two notes up as an eight two note one note up as a nine So we just have our standard Accounting formula formula of running balance and then I can just convert this number to The letter or the letter and the number if I want by tied it in with an X look up to this table and then we created our major scale information that has our No, the the the numbers For the scale that we are in we have the notes of the scale that we are in and then we can list out the Components to create the chords the 1 3 5 being the chords of the major and minor chords And then we can add more fancy stuff the 7s the 9s the 11s and 13s We did this way and then of course we did this way adding the letters in we also made a Circle which helps us to kind of visualize how we constructed this instead of looking at and Visualizing a piano that goes on forever for example We can look at it as a circle that goes infinitely around in a circle And that's how you can see how we kind of built If you use the starting point you just skip every other Every other note in the circle and just keep going around the circle You'll see how we kind of construct these one three five seven nine eleven and thirteen Then we copied this entire thing and said we want to look at the relative modes starting with the minor or Aeolian Because it's it's the one we usually use in Western music the major and minor and then we'll get into more exotic Mode so the next thing I want to do here is say okay this we had this whole thing mapped out It looks good, but this bit is is wrong now because I would like to start this time with the with an a right so so so The a needs to be the one but the the Capitals and the lower case which are going to represent Whether or not we pay it play a major or minor chord are not being represented properly So you'll recall that with the minor all we did is we basically used the same set of notes And we said we're going to take the sixth note Which is an a in our circle here, and then we're just going to map everything out as if that's the one Using all the same notes, so it's just using all the same notes We're just going to say that's our central or starting or tonic kind of note And so now everything is mapped out properly. We have the same notes, but When I list out one through seven I need to be listing out the the a as the one so in other words if I if I looked at this one over here This six needs to be the one So I want to represent it as a one in the minor But I want it to be a lower case because just like we have it over here We're if we were to construct a scale from it We'll get that same minor scale because we're using the same scale pattern Just starting from a different point. So so now I have to do a fancy formula Is there a fancy formula where I can where I can then adjust these so I don't have to adjust them every time Once I copy this over and there is and it's pretty fancy So what I'm going to do is I'm going to say you'll notice over here That that the the the ones that we build a major Chord with are the one four five. It's useful to practice the one four five Oftentimes in both major and minor because those are the ones that are going to be like the same So you'll learn all the major shapes and then the two three and six are going to be minor And then we've got that weird thing with the diminished where we've got that We tried to indicate that with a dot which might be a little bit more difficult for us to pick up But we'll to give it a shot Okay, and we built this Roman numeral thing with this Roman numeral and we made it lowercase by embedding this Roman numeral All right, so let's try to do a fancy formula with taking into consideration all of that stuff So I'm going to delete what we have in here right now And we're going to say all right Let's try to do this in a fancy way so I can copy it over everywhere. So we're going to say this is going to be if Equals if brackets now there are multiple conditions that could be met in order for us to want to have a capital or uppercase versus a lowercase Roman numeral Noting that the uppercase Represents the idea that we're going to be constructing a major chord as opposed to a minor chord So we're going to embed an or function in the if function the or idea Being that we have all of these conditions that we can list if any one of them are met Then we're going to do something to it in our case if any condition is met will make it uppercase Roman numeral That's opposed to or the opposite of the and function Which would mean if we list out a bunch of conditions all of them would have to be met in order for us to apply Whatever we want to apply in this case it being uppercase Roman numeral all right So we're going to list the conditions so this number one which is an a if that is equal to Putting my cursor back down going back on over to the major or Ionian if it's equal to either a 4 a 9 or an 11 then I want it to be uppercase y 4 9 or 11 Because those are the one four five in the major scale So and that and those are the upper case or those are the ones that are going to be constructing a major chord from now But even though we're going to adjust the ordering of the notes That when the notes come up in our in our other modes, they're still going to be constructing the same the same Major and minor scale so that's the first condition next one if this I have to list this one out again if that note is equal to the the 4 Which is all right, which is a 9 right the the relative position 4 which is a 9 note if it's equal to that Or one more condition comma if this note again is equal to putting my cursor back down the relative position Five which is an 11 if it's equal to either of those three numbers then I'm going to close up the or function I haven't closed the if function the if function is still open So I'm on comma next argument in the if function if if any of these are met What do we want it to do? We want to have it an uppercase Roman numeral, which is the default So I'm just going to put Roman or Roman Tab of this number one two three four five six seven the one Representing relative position one in the minor in the minor scale closing that up. What if it's not met? Well, if it's not met then it's either going to be a minor or diminished which means we want to be representing it with a Lower case, so I'm not going to worry about the diminish yet. We'll talk about that in a second So let's just say that we're going to say it's going to be lower case So I'm going to say lower so we and then we embed the Roman Roman and so there it is and then I'm going to pick up the Number here close up the brackets close up the brackets and enter and I think it's going to hopefully pick So then if I double-click on that now if I copy this down note that anything over here I Wanted to be absolute. I don't want these to move when I copy it down So I'm going to go back on over and say this one AM 5 f4 and the keyboard making it absolute This one AM 8 f4 in the keyboard dollar sign before the letter and number making it absolute this one F4 in the keyboard dollar sign before the letter and number making it absolute and then enter So there we have it as a lower case So it's still number one in Roman numerals, but it's lower case that makes sense Because we're talking about the six here because it wasn't an a which is the sixth note is now the one note So we want a one, but we want it to be lower case now if I copy that down Let's see if it copies down like we would think Copying it down. So there we have it now now the three that three represents The seven notice that's the diminished note So it's lower case over here, but I wasn't able to put that little dot in it Which I'd kind of like to be able to do and then here is the four over here that was that was The four was the one and it's uppercase. So this one should be uppercase over here So if I go over here that four is now is now a free position But it's it's still going to construct an uppercase So in other words if I look at these I can still construct these chords Even though they're not in the same order the construction of the chords will basically be the same in other words the the 135 will be the same the distance of the three When I'm looking at the miners will be a whole half or three notes away And the distance of the majors will be whole whole or four notes Away so that's going to be the general idea now now I could say well Is there any way I can get it so that I can make this to have the dot in it? That's would be really pretty neat So let's try that it's going to get messy So if I go in here and analyze this formula the first part is good meaning if it's equal to the one four five Relative positions in the major scale. We want to have it to be uppercase But there's two branches on the lower case So I'm going to embed another if kind of formula on the lower case side of things because we want it either to be Lower case or lower case with a dot like that So then the way we might do it the first way you might think to do it is to say Well, I'm going to put another if and then an or and say if it equals this this or this Then we want you to have it to be lower case and then the lower case with the dot However, it might be a little bit faster to first think about the lower case with a dot Because there's only one condition there and then have everything else be lower case without a dot So let's try to do this if I if I go back on over here and I and I double-click on this formula So we've this is where we completed the first condition if it's not true We have this we just said lower case now I think it's actually easier just to delete the whole thing this last bit and now we have the open if If this first condition is false if it's false We have another branch another tree branch that we got we have two things that we might do we will so we embed another if Function and we're going to say that if and we're going to pick up this number again We're always working with this number if that number was equal to and let's just point starting to the diminished one If it was equal to this diminished one, which was a three Then that's the weird one where that we want to have a dot next to it So how can we do that to put a dot next to it? So if that was true if that's true then Comma then what do we want you to do with it? Well, we want to have it to be lower case with a dot So we want it to be lower So that's embedding the lower then Roman so now there's the Roman numeral in there of The number which in this case is that one looking at the relative positions in the scale and then closing that up and then I'm going to try to add a dot which is a text I'm going to add a text to it to do that Yeah, I'm going to tie it together with an and and then put quotes around the text of a dot Quotes around the text of a dot. So that's what we want to have If it's if that's met if it's not met then comma We just want it to be a lower case and I'm missing another bracket over here So I need a bracket here and There we have it Because that's closing up the lower component. So this second if is still open now So now the next comma here's my argument bar has popped up and we're saying if it's false What do we want it to do? We want you to have a lower Con and then we're going to say Roman and then of that number one of that number one. All right Quite convoluted. We're going to close that up But that should work now this this one that's outside again. We also want that to be absolute We don't want it to move when we pull down so the one that's in am put my cursor there f4 making it absolute and Enter and hopefully it's going to close up the brackets. Let's copy it down and see if it does what we would Expect so there it is it put the dot next to that one. Wow, that was amazingly complicated Now the next thing we might want to think about is when I copy this entire thing Over to the next mode which will be Dorian that we'll work on next time are these Absolutes going to get in the way and I don't think they are because we're still going to always be drawing back to the major scale but note when I when I looked at these items last time and I went in here and I tried to Unabsolute everything I want to take the time to just mention here that it might have been better For me to actually enter the absolutes in there to populate this one and then go back in there once They've been populated and remove the absolute value so I can copy the whole thing over without it getting messed up That probably would have been a faster more efficient way I just want to kind of point that out as we're working on our Excel skills here. So now I think I think this is right now It pulls down to this side now as well because this is just being equal to the ones above it So it should be correct down there. It's also populating in our wheel now So now we have the same wheel But the number one is now being pulled in from here So you'll recall when I when I see this in a circle when I looked at it over here Let's look at the letters so I can see the letters in it. We started with a C and and then we went around the wheel C D E F G a B going around the wheel of thusly and then we have the numbers on The wheel which could be useful for me to kind of see how we're constructing I think it's a better visual tool to see how we're constructing our cords So that same thing is happening here. Let's see it down here So now the first the number one relative Position one if we rearrange everything in the minor means that now we have this as the number one Which is a lower case because it's going to be constructing a minor cord and then we have the number two that's when we get to the diminish that has the nice dot and then The number three is a C and then everything looks like it's populating Properly, I hopefully I got everything correct We'll test it out with the worksheet when we practice with it later But I think everything is looking pretty good thus far So now next thing next time we're gonna try to copy this whole thing over To and do the the other modes starting with the Dorian mode and see if everything copies over Properly and see if we can tweak any of our formulas to make it as easy as possible To copy all the modes to the right and then try to copy the modes below it as well And then we'll also copy over our fretboard so that we can have them right next to whatever We're working with it's gonna be great