 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number eight Get ready and don't fret remember the boards fretted so you don't have to be 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 have a blank worksheet, you may want to begin back there However, if you do have access to the workbook We got multiple tabs down below including the example tab the end result the final product in essence the answer key The starting point tabs tying into the starting points of the video presentations the blank tab where we started with a blank worksheet and are Continuing at this point in time quick recap of what we have done thus far We've listed out the musical alphabet with letters the traditional way here a a sharp a sharp being represented With an a and a b or b flat whatever you want to call it b c C sharp d d sharp e f f sharp g and then it goes to g sharp and then it repeats again So we've got the 12 notes and we repeated it two times over within numbered them And I'm going to call these absolute numbers of the notes They're not relative to the position in a scale or anything like that. These are absolute So that's a one we combined them together I can call it number one absolute note or an a or a one and then we built a scale from that We started with this for for the C scale and we used our major Our our major formula whole whole half whole whole half Which you can also say as two notes away two notes away one note away two notes away two notes away One note away, and then of course that repeats We've repeated it a few different times so that we can build our scale Which is going from C to C here and then it starts over C to C here and then it starts over and we can imagine it going up To infinity this way kind of like you can imagine a fretboard I mean a guitar a piano a piano board Going on forever although it goes up in octaves in the piano You can imagine it just repeating in terms of the actual notes And then we built our table on this side in terms of numbers so we can look at the Differences between the notes as well as this way so that we can now see our notes that are in our scale in this case The C scale we can see the Roman numerals telling us what kind of chord We should be playing related to it which would be either a major minor or diminished And then we built out the notes that could be in an Actual chord which are of course coming from the scale that the scale was built from the Absolute all of the notes in the musical alphabet these first three being What we would need to construct the major and minor chords in these columns and then we can add the other ones Which we would call seven nines thirteen elevens and thirteen Based on us skipping every other note as we can see I think most easily in Our circle over here, which is what we constructed next which is to say when I construct my actual Chords what I'm going to do is say this is the one and then I'm going to skip and so this three represents the relative position in our our Scale right so now I'm taking the three note in the scales So I skipped one I'm over here and now I've got the fifth so now we're over here So one two three four five right and then the seven and but then you get to the nine and whatnot That means we've wrapped around again, and we're starting over. That's why I think this circle Is a useful way to see it because instead of seeing the piano keys going on forever into infinity You can see a circle just repeating on to Infinity so now what we want to do is say I can I copy this? I want to see it not just for the major, but also for the minor and the other The other modes so let's let's so we can think of the modes two different ways and It's useful to say okay if I'm on the key of C. I can look at the relative Minor mode which would mean all the notes are the same But meaning all these notes are the same, but instead of counting it from C I'm going to count it from the relative minor, which is the six is an A and so we could we could see it that way or You might also when you're playing it's practical to then say I would also like to see C minor right C minor has different notes in it. It's not the same scale But you might be able to transition pivoting on C from C major to C minor or any of the other modes It's easier I think conceptually to first think about that first way to say I'm going to use all the same notes But I'm now going to just focus on the minor note I could focus on any other note like I could do the D that would be a Dorian We'll get to that later or the E and I could do either any of these notes and focus in on it I'm going to start with the minor though because that's the most common you go from a major to minor Those are usually the most useful transitions to be making so I'll do that first even though In theory you would think the next mode would be the Dorian, but we'll get to the Dorian next time So how can I do that? I want to what I want to do then is Basically copy this whole thing, but then think about it as though the a is like the number one and a Dorian Kind of mode so I'm going to say okay What what what I can do is I can try to copy this whole thing and then change Change the formatting of this table to represent The formatting of the of the mode of a minor. I'm not doing Dorian. I'm doing minor So let's try to first to copy the whole thing and say go What if I just start off by copying let's copy the skinny well not the skinny do I want the skinny? Let's copy the skinny going from the skinny all the way out to the end of our circle over here And then I'm gonna right-click and copy that as my starting point and let's see what we can do with it I'll put that over here on BB will right-click and paste it boom So now we've got the same thing and a lot of the relative information should pull over Although it's gonna get messed up sometimes when we use those absolute references and whatnot So we now we want to think about how we can kind of change this table. Let's first think about the table I'm gonna use the the relative Scales are gonna be the same meaning now I want to say this represents the relative one position of a minor now instead of The major so maybe I should change the title first here, so I'll say this is gonna be the minor or or a I think this is how you spell it Aeolian I hope which is the six Sixth note from our major That means a relative major in other words Now we're looking at the minor which you can also call the Greek term Aeolian if you're talking in terms of modes But major and minor we often use like the English terms I guess of major and minor the other modes We will use the Greek but you might not cure it called Aeolian especially if someone's trying to kind of confuse you or whatnot But it's gonna be this the sixth note So what I'm trying to reference here is that it's the sixth note from The relative major so a lot of times if you're trying to say I'm playing in minor It's useful to then think about what the relative major is of it and and so you'd have to then kind of work backwards We'll talk more about that in a second to figure out what it is the minor of right? And so in this case we're looking at an a which is going to be the relative minor of the C major All right now. Let's see how we can just fix this table So now I'm gonna say this table needs to be fixed So this four up top what I'm gonna do is say this is gonna be equal to and I'm gonna say equal and scroll over to my Table over here and I'm using the same notes as this table in the major But I'm starting on the sixth note which is down here. So now I'm starting on this one. That's gonna be my One note so I'm gonna pick up that one right here And then I'm gonna make this blue instead of green because this is not one that I want to change So I'm just gonna keep that as is and then I want to be copying my formula the formula can't be The same formula whole whole that's the major formula, but if I can copy the same formula from The same point over here, right? So this if I go over here, this is whole whole half whole whole half That's the formula if I started counting from The major position, but now I'm gonna start counting from the sixth position, right? So if I start counting From the six if I'm here the next step up is gonna be two so notice I'm gonna have the same pattern, but I'm starting from a different position So I'm gonna start here and then it goes whole and then then the and then the whole whole whole half again So I'm gonna pick up this two Instead I'm gonna delete everything below it And if I copy that down so that takes me that's right And then I copy that down so that looks good and if I copy that down to here I'm at 11 and let's go one more that brings me to one and then so now I've gone from a to a So that looks good. So now you can kind of see the the whole whole half in there But we're starting from note number six so you can see why we're basically doing the Same thing we're using that we're gonna end up with the same notes But you can think of the pattern being different because we're starting from the pattern from the sixth note instead of on the one note and Then the pattern will repeat now. I'm actually gonna do it a different way. Let's actually delete this I think it'd be easier to see this way I'm gonna pick up that one and then I'm gonna delete all of this stuff and Then say let's just copy this one down It's gonna copy from that relative position Down and so there now it's gonna be Repeating down here another one and now because I what I'm doing is I'm copying I'm gonna reference it over here so you can see where it's coming from I'm just copying those notes and the pattern is gonna be repeating because we're picking up all the same notes So I'll say Do it that way But if I keep doing this I can't go all the way down because I'm gonna run out of space So what I'm gonna do instead is I'm gonna say now the pattern is repeating So instead of putting a one here, I'm just gonna say this equals the one above it And now when I copy that down, it'll keep copying that same pattern down which should repeat and It just keeps on taking the relative position above it and now instead of me pulling over the formula pattern I can Create my formula pattern this way. This is gonna be equal to this Minus this so now this is two notes away. So if I copy that down, let's copy that all the way down Now now this doesn't work. Why because it's two notes away one note already two two one two And then we run into a problem here because we end up with this negative number So now let's try an if formula to fix that so say, okay, how can I fix that to make this? Work I could say let's delete this. Oh, not that. I'm gonna delete the whole thing Delete this and say, okay, I'm gonna say Equals if logic function we're gonna say if this Minus this let's put more brackets around it another set of brackets to make sure I'm picking both of those things up if that whole thing is greater than zero then Comma what do you want it to do just do that take this you can't see the cell but you could see Right, what do you want it to do? I'm gonna put it down here to take this minus This and then comma. What if what if it is negative? Well, if it's negative, I still want you to take this minus this But then add 12 to it So it's a negative number we add back the 12 because there's 12 notes and that should bring us back to the proper position So let's see if that works. Let's put our cursor here double-click on down So now you've got the two away Then one two two one and so on so there we have it now if you if you count from This position remember the relative. This is the six right if I look at my circle over here. I Had I was on four if I count that as the major the one now. I'm over here. So so now I'm going To the four right from here's my one if I go back to the four and I start from the four I'll see that same pattern, right? So if I start from This note then you've got the whole whole half whole whole whole half same pattern But now I'm starting from the six and starting the pattern there, which means you've get a you get a whole A half whole whole Half whole whole right, so it's just a so it's it's basically the same pattern You just have a different starting point You're gonna end up with the same notes and then you've got this formula, which I think is populating Properly Actually, it's not because of the absolute references involved. So this one is taking the interval From the root. So I want to see how far the absolute differences Absolute distance is From each note to the to the root. So I'm going to try to redo this. I'm going to say this is if brackets and I'm going to do the The test of another brackets and I'm going to say if this number Minus this number Closing up the brackets is greater than zero Then comma what do you want us to do? I just want you to take this number Minus this number But what do you want to do if it's less than zero? Then I want you to take again this number minus this number, but and you guessed it add 12 to it, right? We're going to add 12 to it and then we get back to our two I need to make some of it absolute because I want this number to move down But not this number up top. So I'm going to go up top and every time I see that The be two which is this one I want to make it absolute now if I make it absolute that will work here But I'd like to be able to copy this all to the right at some future point and not have to redo the formula again When we go to the dorian mode. So what I'm going to do is say I need a mixed reference I don't want this number to move down when I copy it down, but it can move to the right when we eventually do that therefore Uh, I want it. I don't want it to move down. So a dollar sign before the number Not before the letter mixed reference And so I'm going to put a dollar sign before the two over here as well And then a dollar sign before the two Over here as well and then enter and then I'll copy that down just double click in the fill handle this time And I think it's doing what we want now. So now this is This is uh eight away eight minus one And then 10 away and then 12. So I think I think it's populating the way we want it to populate So now let's look at our table over here And say we looked up these numbers And so so obviously this is pulling I can and I can use these tools up top to see where it's coming from I use this this is actually in the data tab and and uh, you can you could see where they're coming from by going To actually it's in the formulas tab formulas tab formulas and the trace and the trace depends and then the remove So you can kind of see where your formulas are coming from so that becomes useful So I can say okay, they're coming from here, which looks like I might have used some kind of absolute reference in here to do that So I'm going to go okay I'm going to turn that off and say if I go into here. I can see oh, yeah, there's the absolute reference that we had to use or that was used In order to uh to do those so I can see that so let's repopulate this so I'm actually let's just see if we can Uh remove this and just say I'm just going to delete this whole thing and try it again So that's going to be an equals an x Lookup and then I'm going to say brackets the lookup value is going to be this number Plus this number now. I don't have to say plus right there because this is zero But I want to pick those up because I want to be able to copy this across Whereas the next one's going to be this plus the two right this plus the four So then I'm going to say comma the lookup array. I want you to find that number excel Over here control shift down So there it is there and then comma and then what do we want to return? We want the return array to be for this one Just uh the numbers I'm going to just pick up the numbers to start with And then we'll do the letters are down below So that one and enter so it picks up The one which is the a let's bring it to back to a c. Well, that's the relative minor is an a I thought we were to say so the relative minor of the c isn't a represented by A one. All right. So that looks correct now. I want to be able to copy it So when I copy it, I want this part To be able to move to the right, but not this number. So let's see But you know what might be even more useful long term Is to try to have everything relative and then when I copy the entire thing And try to do the dorian and the other modes. I don't have to repeat this. So let's test out that So I'm going to do the long work this time Which might make the work easier when we copy this whole thing over next time So I'm just going to repeat every time this time. So we'll practice the x lookup Multiple times so x lookup tab. I'm going to say now it's going to be this Plus that one and then comma And the lookup array is going to be this control shift down And then comma the return array is going to be this one And control shift down and then enter and I'm just going to do this every for each one Which I know is tedious, but I think it might help us next time when we do the dorian So I'm going to say this equals x lookup and then And then we want the lookup Value is now this plus this and then comma the Lookup array is going to be control shift down comma And then the return array Is going to be here control shift down enter and then I'm going to do it again Equals x lookup tab lookup value is this Plus this comma And then we'll pick up the arrays control shift down comma and this one and control shift down and then enter And I might be able to just cop. Maybe I can copy these arrays And that'll make it a little bit easier. Maybe I can say copy the control c And then I'm going to do it here. This equals x lookup And I'm going to say this plus this comma control v pasting that in And there's our arrays. That's not too bad. So this equals x lookup And I'm going to say this Let's keep it the same this plus this And then comma boom And so there we have that and then This one is going to be equal to this plus this comma control v And That didn't work k posse That plus 12 and the lookup array is I don't think I put the x lookup in there x lookup and then this plus this comma pasting boom And then this is going to be equal to x lookup And then this is this one now plus this comma control v I'm going to say tab this time and then this equals x lookup Tab And then we're going to pick up this plus this comma control v tab equals x lookup This plus this comma control v tab equals x lookup this plus this comma control v tab equals x lookup this plus this comma control v tab equals x lookup this plus this comma control v tab equals x lookup this plus this comma control v. I know this is tedious x, but I think but again, I think it'll help when we copy it across this. There is it. We could use absolute references and whatnot, but that might be a short term. So we'll test out the long term solution. So this is x lookup. It's going to be this plus this comma control v tab. This equals x lookup this plus this comma control v tab. This equals x lookup. Let's do it the same way. This plus this comma control v tab. This equals x lookup this plus this comma control v tab. This equals x lookup this plus this comma control v tab. This equals x. lookup. This plus this comma, control V. tab. This equals x. low cup, tab. This plus this comma, control V. tab. This equals x. hookup. This plus this comma, control V. tab. this equals x lookup this plus this comma control v tab equals x lookup this plus this comma control v tab and we will repeat the process multiple more times and this lookup is brackets this comma control v equals x this comma this plus this comma control v enter this equals x lookup this and this comma control v this is equal x lookup this and this comma control v and this equals x lookup this and this comma control v this is now I have to have x lookup this and this comma control v and this equals x lookup this and this comma control v this is x lookup this and this comma control v this is x lookup this and this comma control v this is x lookup this and this comma-control-V this is x lookup this and this, comma-control-V, x look-up this and this, comma-control-V x equals x lookup this and this, comma-control-V This is x lookup this and this comma control V equals x lookup. This and this comma control V equals x lookup this and this comma control V. One more round. I didn't hear no bell equals x lookup. this and this comma control V equals X look up this and hold on a second is that right yeah this and this comma control V equals X look up equals X look up this and this comma control V and X look up I'm getting excited cuz we're almost there this and this comma control V and this equals X look up this and this comma control V and this equals X look up enjoy these last two cuz this is it and we're done we have to stop after that equals X look up this and this and then comma control V okay now hopefully I got that correct and note that when you're looking at these worksheets you could compare these to to an actual worksheet that you look up online right and just make sure that you have all your numbers correct once we get down here you can kind of double check down here to you know any worksheet that you find in Google to just double check your numbers I don't make any guarantees of not making a mistake as we go through this but we'll practice with those worksheets afterwards I'm hoping and then that'll help us to kind of drill down and fine-tune any mistakes and anything that we might be able to improve on possibly okay so now the other thing is up top we have these numbers now this one if I look at this this is my absolute distance now this one doesn't work again if I go here and I say okay what happened with this one and I traced this out it's like okay now it's picking up an absolute from over there that doesn't work okay so what I wanted to do is to take this minus this number which which should give me a three right so I'm gonna say alright let's let's try to do this again and I'm gonna say this one's always gonna basically be zero I don't even need this one because it's always zero let's just start at this one this is gonna be equal to and I'm gonna say an if function again so let's do an if thing so we'll say this is gonna be equal to if brackets and then I'm gonna put in another brackets around I want this number minus this number and if that is brackets if that is greater than zero then what we want you to do is just take that number if that's greater than zero then I need a comma then comma what we want you to do just take that number minus this number but if it's not what do we want you to do we want you to take this number minus this number and then add 12 to it because it'll be negative there's 12 notes in the key and the 12 should bring us back to the correct point so that comes out to a three that looks correct now when I copy this to the right I get to the same issue I'm like well I want to make it so I can copy it to the right but I also want to make it so that I can copy this entire sheet and make the Dorian mode where everything is relative so I kind of want to reduce the absolute references if I can so is there a way I can adjust this without messing it up so I can copy the entire worksheet over so for example this one I want this number to move to the right I want this number to stay where it is so there's I don't think there's any way I can really do that so that so that I can copy this over because when I copy the entire worksheet over I want it so I want the letters to move to the right so again I think it might be worthwhile to do the tedious thing here of of re-inputting it without absolute references so that I can then copy the entire thing over later so what I'm gonna do is just repeat this it's only a couple times this time so it's gonna be if brackets brackets this minus this I still need to do the logic test here because when I copy to the Dorian mode then it might work out that the logic test is going to be necessary so this minus this brackets if it's greater than the zero then we want you to take this minus the first one and if it's not then I still want you to take this minus the first one plus 12 close it up and then tab let's do it again equals if brackets and then another brackets this minus this if that is greater than zero then I want you to just do this minus this otherwise I want you to do this minus this plus 12 tab it's gonna close it up for me I say yes this is gonna be equal to if brackets brackets this minus this is greater than zero then comma I want you to take that minus that but if not comma I want you to still take that minus that but add 12 and close it up this time tab equals if brackets brackets this minus this close up the brackets is greater than zero then comma I want you to take this minus this comma if not though still take this minus this but then add 12 close up the brackets one more time equals if brackets brackets this minus this brackets is greater than zero then comma do this minus this but if it's not comma still do this minus this but add 12 to it and brackets okay so we don't have any absolute references so now when I copy this whole thing over hopefully it'll move over appropriately okay so I think I have every I hope I have everything laid out here properly so now if I move down here this whole thing should populate correctly because most of this is just pulling from the one above and now I'm looking in here now notice here it's it's taking the x lookup from this table from this table is that a problem not really because we are we're using the same notes in this scale as we are in that scale over there so it's not really a problem that it's pulling from that table and it won't be a problem as long as we're going to the right but when we start copying down then we're gonna have then we're gonna have a different table so it'd be better really if I can get it from this table there's two solutions here to make my copying easier I could take instead of using this table I could reference everything to the entire scale over here I can use this table because that'll work no matter what it's kind of nice to use the table relative right here if I could because that one it's easy for me to fix things because it's right next to what I'm looking at so I'd kind of like to use use this table to pick up this information so I'm thinking okay let's let's see if I can adjust these ranges so I'm gonna try to adjust these and try to do it as fast as I can here so and this will help me with the long term when we copy it down so what is this doing again all I'm doing is saying I want to look at this note up top I want to find it over here in this columns that one and I want you to return the 1a so that's an x lookup let's redo this one I'm gonna say this equals x lookup I'm gonna pick up this is the value I want to look up and then comma then the array the lookup array I want to be here control shift down and then comma the return the return array what do we want it to give us back then pulling this down can't pull this grab it without clicking on anything and then pull it down return array this one control shift down and enter so that's good now what I'd like to do is replace this bit in all of the other ones so that so that I don't have to use absolute references and then I can copy the whole thing across I'm gonna double click here I'm just gonna I'm just gonna re format this and say control V tab I should get to the same number double click and then control V tab double click and then this bit control V tab double click I'm gonna pick up control V tab double click and control V tab double click so again tedious work here but I think it's gonna save us once we start copying all the different modes so I'm gonna say this one control V tab this one and take this control V tab this one this bit control V tab this bit and we're gonna say control V tab ah k-passo this bit control V tab this bit control V tab this bit control V enter and then this bit control V tab this bit control V tab so you probably get the pattern here so if you want to fast forward you know you can but I'm gonna show the whole process just to be thorough like to be thorough don't want any un-thoroughness like you didn't show me how to do that step yeah well I showed you you just have to watch like 20 hours of stuff and you would see like in our 7.4536 that's where it was that's where you should have been able to find that and pick it up so we'll say this is this one and as my old school would say well you got it you know what you got to do is you've got to trim that problem down to under five minutes if you could just trim if you could just trim the whole process down to under five minutes that would be great that would be great well that I don't think I can do that because it's it's kind of it's more complicated than a five minute thing well yeah but you know the five five minutes that's what people's attention span is and you have to cram everything into people's attention span or else it's your fault that people don't know what that what's going on but that's that's impossible you can't do that no come on let's have a little optimism here five minutes you can learn everything there is to know and excel if you had any skill if you had any skill at all with your teaching capacities then that's then you could do it in five minutes and all of your knowledge would be given to someone as though they jacked themselves into the matrix like neo in the movie that's just a movie that doesn't really have you can't really do that oh come on come on five minutes okay so there we have it so so hopefully I did that right we'll check it out as we go forward but but in the future we'll and I think these all copy over relative as well so I'm gonna have to possibly change the Roman numerals because the Roman numerals are off now you'll note which is another kind of weird formula so we'll continue with that next time because we're going kind of long here