 Guitar and Excel, interval and modes, complement and parallel worksheet part number four. 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 started in a 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 there's six tabs down below. The first two tabs representing the end work, the finished product, the final worksheet, the numbered tabs tying into the video presentations where we worked on that portion of the worksheet. We're now on the 3018 tab where we're going to start at the same point we ended last time. So in prior presentations we started out building the musical alphabet. So we listed our musical alphabets. We numbered the musical alphabet. We then put the letters and numbers of the musical alphabet. We then wanted to make the alphabet relative for us to start at any one point in that alphabet. This is the key. So we put the four there and we can adjust this to anything we want which adjusts the format below it and so now we can start at any point and run through the musical alphabet from there. We then looked at our intervals in terms of absolute intervals, the easiest way to see it meaning what's the distance in half steps or just notes up from the starting point whatever's in this key in this case the C which starts at a four and these are the intervals in relation to it. Then we wanted to name those intervals so we gave them the fancy names that we have to memorize to communicate with people. We then wanted to put a symbol for those intervals so that we can easily put those symbols somewhere instead of having to put the fancy name and then we put the number of the absolute as well as the symbol name. We also constructed our fretboard on the right hand side. So now we're going to continue on and we want to think about this time another worksheet to build our our scale starting with the major scale. So I'm going to start here we're going to say this is going to be the scale I'm going to call this the relative number so this is going to be the relative number of the scale and I'm going to say home tab format painter let's wrap that and I'm just going to say it's going to go from one to and I'm going to bring this on up to 20 so I'm just going to bring this down to 20 down here that'll make more sense in a second. So we got numbers one to 20 and then we have a formula formula and before I put the formula in place let's start with our note our note in number format now this starting point of the note in the number format is going to be equivalent to our key over here which is that number four so we'll start with that number four that's going to be our starting point I'm going to format paint the headers over here home tab clipboard format paint these headers now we're going to do our trusty formula for the major scale which is often called whole whole half whole whole half or you might call it in terms of just number of notes it's two notes away two notes away one note away two notes away two notes away two notes away and then one note away why is that formula the case that's beyond the scope of this presentation we're going to take that a priori we're going to trust the age the ancients here of the music masters that's that's the that's the formula they gave us so we're going to put that into place that's how you get to a major scale you start from whatever point you're going to start at in this case a c or a four and then you go up two whole step two whole step one or half step two whole step two whole step two whole step and then one now note that if I look at this in terms of numbers it makes it very easy for me to just do a running balance here to say well if i'm on note number four which represents a c then i go from four five six which is going to give us the d how so i could do a formula i could just say this is going to be this plus this all right and that and that's pretty nice and easy to do that's why numbers are useful you can count with them but as i go up here i go beyond 12 so that's a problem so i need to come up with some formula for it not to do that and so i want a fancy formula instead of just kind of figuring it out by hand so i can copy and paste everything so we'll do a logic test and it'll look something like this equals if brackets and we're going to say if that number four plus that number two if that is less than 13 which means it's going to go up to 12 because there's 12 notes in the musical alphabet not going to 13 then that's when i put a comma and the next argument is the value if true what do you want to do if it's true well if it's less than 13 i just want you to take that four plus the two which would be six what if it's not true what if in other words it's greater than 13 well then i still want you to take that plus that the same way but it's going to be if it was 13 i'd want you to subtract out 12 which gets me back to the one which will take me around the circle so i want you to take that minus 12 there it is it looks fancy but not too bad and it gives me the six so let's copy it down and test it out so i'm going to copy it down and all the relative references bring it down so six seven eight up by two eight plus one goes to nine nine plus two goes to 11 11 plus two didn't go to 13 but went to 13 minus 12 or you can think of it as 11 12 and then back to one which is representing an a and then a three and then uh to the four okay so back home so now i'm going to repeat this formula so i can repeat the formula by saying equals the two and then when i copy this down it'll copy to the next two down so it that'll just repeat the formula and i'm going to repeat the formula down to 20 i'm not going to go exactly out uh to 24 this time uh so it repeats down to 12 uh and then down to 20 a couple times over because that will help us when we make our worksheets which we'll do on the right so now let's copy this one down as well and we'll have just the repeat of the notes so there we have that okay and then i want to give the let's do this one which is going to be the number and the letter so now i want to see not just the number but the number and the letter so let's format paint home tab format paint that to here so i'd like to make that four in other words turn into a four with a c next to it as well so what i'm going to do is i'm going to go to uh my column on the right where we have these two combined together which is going to be over here and i'd like to get that that four c so i'm going to look i'm going to do my x lookup thing again i'm going to say if you see this four i want you to look up that four in this column and then i would like you to return to me uh i would like you to return or i could do it basically in either these column it might be better to do this column i'd like you to look it up in this column and then return to me the related letter and number so you're gonna look up that number four and give me that c four c so i'm going to use the trusty x lookup again so this is going to be equals x lookup and so the lookup value is just going to be that number four comma next argument is going to be the lookup array so i want you to find that value over here i'm going to go all the way to this one in that array and then comma next argument is going to be let's pull this over so i could see the argument return array we want the return array to be this column and so that should provide me or look up that four and give me that four c so there it is now to copy it down i'm going to double click i have to do some absolute valuing because i don't want these arrays to move down so i'm going to put an absolute value f4 right here dollar signs before the letter and the number f4 right there dollar sign before the letter and number f4 right there and f4 right here dollar signs before the letters and numbers enter then i can just double click on the fill handle and copy it down so now we have our formula and we can see our formula in number format and in terms of the letters right so it goes from a c which is i'm calling a four because it's the fourth note in the musical alphabet plus two gets me to a six which i call a d a six out which is a d but i call it a six right six plus two is eight i would call it an eight which is an e and and remember that these notes over here these numbers are absolute they are not relative they are what they are and but the positions are going to be relative right so so we want to just point that out but an eight plus one is the nine that's an f nine plus two is 11 that's the g 11 plus two is back to one which is an a and one plus two is the three which is a b and three plus one is four and that gives us our c okay uh so then uh i could list out then the intervals but i think we already have the intervals basically over here right so this is going to be seven out of the twelve notes so now we've we've got seven out of the twelve this will become more clear when i make the next worksheet so let's make this small and let's make the next worksheet let's make another skinny ap and this i'm going to call the major or ionian worksheet so i'm going to call this the major or ionian worksheet and i don't need this bit i don't think so remember that the major scale is kind of like our master key that's how i think most of western music thinks of the major scale that looks like how the baseline that everything else was constructed on although again i think if you had anything else you can also use it as the key because everything is relative so but it's easiest to map everything out first off with the major scale thinking of it as kind of like your master key because that's naturally how everything it looks like was built so then i'm going to say that we're going to be doing the i'll call this a long name uh for the relative scale intervals from the chord root and then i'm going to say this is going to be this will make more sense in a second i'm going to say this is the chord scale interval so i'll get back to that in a second so then i'm going to name our positions i'm going to start here in ar4 which is going to be just one two three four five six seven in other words we're taking our full uh 12 notes uh scale our 12 notes in the musical alphabet and we're pulling in just seven of them and so and then those seven notes are going to be you know based on this this information right here the the c d e f g a b and then the c is a repeat now to populate the actual cells in our worksheet we know they're going to be this is going to be the starting point but i'd like to come up with a formula that i can use that will be uniform throughout the entire worksheet so to see how to build that i'm going to go up here this is going to be the relative scale intervals so i'm going to say this is going to be zero two four six eight ten and twelve that'll make more sense in a second but the general idea would be that we're going to be starting at each of these starting points and then uh and then increasing by those intervals uh in order to get the notes that we're going to have in our worksheet i actually should have pulled them over one i'm going to pull these over one more let's put them over here okay and then i'm going to make from a s to a y a little bit skinnier we'll skinerize them okay so then what i'd like to do is i'm going to say okay this first i know what this is going to be this first cell is going to be a c or a four c that's what i want it to be but i want to be able to look it up in such a way that i can copy the formula across this whole worksheet so to do that i'm going to do an x lookup tool so it's going to look like this we're going to say this is going to be an x an x look up and then i'm going to say brackets and i want to be picking up this one so i'm going to look at that one plus this interval of zero the distance from that one which is zero because there is no distance from that one at this point and then we're going to take that and then comma that's the lookup value and we want to find that over here in the relative scale positions where i listed just one through 20 notice we didn't repeat here because now i'm going up i'm going to continue going up instead of around in a circle so i'm just going to continue going up to 20 here for our formula and then we're going to say comma and that's going to give us the uh the what did i did i did the lookup array and i should be getting the so i re-typed and hit the the lookup array is here and then i'm going to then go comma and the return array then i wanted to give us here so look that up so what's it going to do it's going to look up this value which is one plus zero or just one and i want you to look that up over here which means it's going to find that one right here and then it's going to return the one that's relative to it which is the four c so enter and there we have it okay so that was a little bit complicated but now we should be able to copy that down if we can get the the formula correct in terms of mixed references now so now i'm going to try to make this even more fancy over here so we're going to say we have our lookup value so i took this this cell right here i want it to be able to move down but not to the right right so when i copy it it doesn't move to the right so that means i need to make the letter side of things absolute so i'm going to put a dollar sign before the letters so i'm going to say dollar sign here and then this one up top this one i wanted to move to the right but i don't want it to move down when i copy it and so that's the numbers so i have to put a dollar sign before the numbers on this one to make it mixed and then this one is is the lookup array i want the lookup to array to be the same all the time so i'm going to select f4 in the keyboard here f4 in the keyboard here same with the return array i want it to be the same f4 f4 so those are absolute references these two i have the fancy mixed references and let's see if that works i'm going to say enter now when i copy this down i should get my c d e f g a b so now it's picking up the five plus the zero so this is the fifth note and then the interval from there is zero so there it is now let's copy it this way and i'm going to say and so now we've got this one is a c to an e so what happened here it's taking the one and then it's going up an interval of two so it's going up to a three and the three over here is an e right so the third relative position from the four so four to the three is an e right and so that's what's happening here so we're taking the four the four plus the one so let's copy it down this way and see if we can make more sense of it down this way if i copy it down this way and i pick up like this cell now i'm saying i'm starting on the fifth the five note and then we're going a four uh interval from that five note right so five plus four uh is going to be five six seven eight nine so and so i'm picking up this nine right here and that's going to return then the uh six d so that's how this is being constructed it's a little bit that can be a little bit confusing but the general idea here is what we have now is we have our our notes in the scale c through uh c d e f g a and then b and then what happens on this side is we construct our chords uh within that scale by taking basically every other note in the scale so if i look at this scale i say how how would i construct the one chord it would be c then skip the d to go to the e and there's the e and then you skip the f and go to the g there's the g if i start on the d here i do the same thing i could start on the d and i could skip every other note in the scale gives me to an f in the scale mind you not in the whole musical alphabet and then skip every other note and that gives you uh the a so that's how this is basically being uh constructed we'll talk more about that in a second but then the the chord intervals if i put them here will usually be one three five seven nine eleven and thirteen or i can just think of it as taking the last one plus two because when we think about uh those when we think about the intervals in like a chord we're not talking about the interval from the root of c we're talking about the interval from the starting point it's relative right so when we think of a chord we usually think about the one three five of that chord we're not talking about what with the scale that we constructed it from everything here being constructed from the c scale we're talking about the relative note of the chord so this would be a d minor which would be starting on the d and then taking every other note that that happens to result in a d minor because of the intervals which we'll talk about more shortly so there's going to be our our worksheet now i'm going to make this a little bit smaller here and then i can also put the roman numeral so this is naming the one uh two three four five six seven relative positions of the notes in the chord now we make each of these so that these are the notes then that i can number i can say what the relative position is i can say it's this is the three note an e is the three note of the c chord but i also might want to know whether or not i can make a major or minor chord from that because we built this whole thing here we built this whole thing based on the c major so so then the question is i just did the same kind of technique to build all these things we just took every other note uh in the scale we took each of these starting points and then took every other note is what happened if i took this e we start on the e and then we take every other note e g that would be the e would be the one this would be the three this would be the five right and then if we did it up for the f then we would say this is the one the a is the the three and then the c is the five that's the one three five however the three in particular here when we're just looking at those first three notes in any case which is the most common thing to first start off learning has different intervals because if the interval is three notes away then it's a minor third and if it's four notes away it's a major third so the next question is well how can i easily know if i start on each of these notes in the scale whether or not the chord that would be constructed from the key of c in this case would be a major or minor and we can do that with roman numerals so by having upper and lower case roman numerals so i could call this i'm going to call this equals roman uh and then pick up that one boom so now i've got an uppercase one i know that that happens to be a major and i know that because when i look at the interval between the four and the and the and the eight or e and c it's a four note distance or a whole step and a half so whatever you want to go four notes right that that's a major third and that's what we're trying to get a handle on with this interval worksheet so this one however is going to be minor so i'm going to say this is going to be lower there's another formula tab and then roman tab and then that too so now it's going to be a lower cased roman two which means it's a minor it's a minor because from an interval standpoint the nine minus three minus six is three so it's only three notes away it's a minor third so so we can represent that easily over here it happens to be that the one four five will always be major scale constructions in a major scale and the two three and six will be minor and the seven will be diminished which is similar to a minor but has a flat fifth so we're going to say this will be lower i can actually just copy this one down let's copy this one down lower three this i'm going to copy this one over here because it's going to be upper copy that one there and then this one uh i'm going to copy the lower and then this one is going to be a diminished so i'll copy the lower and then i'm going to add something to this one i'm going to add a dot for the diminished so i want to say that and so i tie that in with an and sign and then i want a text of a period so i'm going to put uh quotations period quotations that's how you type text and there it is so now it's got a little dot and that's going to indicate that it's diminished maybe i should use a better symbol than that some people might be picky on that but that's going to be my distinguishing characteristic i'm trying to keep it tight and small so it fits in cells easily all right so then i'm also going to put on the outside here this is where i'm going to label that that it's a c uh c worksheet so this i'm just going to say this equals that four c right there so we pull that in okay so then i'm going to make this one smaller so we're going to lose some of that wording on the header but that's okay uh i think we're going to say that's okay and then i'm going to make this whole thing this whole thing is like a header so i'm going to make all of this black and white home tab font group drop down let's make it black and white and then this whole thing is kind of like a header so i'm going to make it black and white so i'm going to go home tab font group black and white uh hold on say black and white there we have there we have it and then i'm going to make this part in the middle let's put some brackets around it home tab font group and we'll bracketize that one let's put some brackets around this whole thing that we did put some brackets around that home tab font bracket and then see if i can make these a little smaller that's squishing the words i'll probably i'm okay to wrap the words i want to make it skinny the words will be wrapped i'll make it as thin as possible it's going to get ugly here as we wrap the words but i want to make it thin it's important to be thin but you're becoming anorexic no it's not i'm not anorexic i'm just trying to lose a little weight all right let's copy the format of this worksheet so i'm going to copy this whole worksheet and i'm going to put it down here as well i'm going to paste this down here and then i'm going to adjust it so so i'm going to say let's get rid of everything in the middle right now and then i'm going to say everything i want just really the formatting let's actually i'm going to delete the whole thing i should have just pasted the format and then try to pick everything up so i i make sure that these two things are connected so i'm going to pick everything up so that i can copy and paste this by saying this equals the cell up there i'll copy this down to to there and then i'll copy these two across to here and i'll say this is going to be equal to that uh four and then i'll i'll do the same thing down here i'll say this equals well i can just copy these down as well i can copy these down so now they're all tied together because everything here is tied to what is up top so what i'd like to put in here then is the intervals so remember the intervals are on on the right side over here so we have then our our intervals over here uh right here so i want to put both the distance and the interval symbol so what i'm going to do is compare all the intervals to the the top to the c so how can i do that i can say okay this is going to be doing our x lookup thing so we'll use our x lookup equals x lookup tab and then what's the lookup value it's going to be that c4 and then comma where you want to find that value what's the lookup array it's going to be over here the lookup array is not going to be the numbers but it has to be the number and the letter because we're looking up that c4 and then another comma which takes us over here to the return array what's the return array i want to give us both the the number meaning how many units away it is how many steps as well as the symbol like perfect first minor second and so on so i'm going to hit control shift down to see it that time or you can just select the whole thing but i'm trying to get fancy so the control shift down just to note that and enter that should give us the zero perfect first so that kind of ties out to these intervals up top which are which i'm just calling one three five seven right and down here i'm calling it more formally the perfect first and you know and so on so if i if i copy this down or to the right i want this cell to move relative down and to the right but the arrays i don't want them to move so i'm going to f4 in here dollar sign before the letter in the number f4 in here dollar sign before the letter in the number f4 in here dollar sign and f4 in here dollar signs enter now if i copy this to the right then you could see that we we now have the minor third so i still have this three up here that means it's a third right it's the third but it's really a i'm sorry a major third which means it's four notes away which is gives you this four because it's a major chord and this fifth here a perfect fifth corresponds to this five which is kind of like the more informal format of showing it right it's the fifth but it's actually seven notes away which you can see eleven minus four is seven notes away this is going to be uh the seven the seven uh which is which is going to be more formally a major seven which is 11 notes away which is a little bit more difficult to see but if you count the distance around the horn from uh the four around to a three you're going to get 11 right four five six seven eight nine ten eleven twelve one two three so we can talk about how to do that more easily basically later and whatnot but that's the idea now if i copy this down i could copy this down as well and there we have it so but notice that this cell down here like this i d if i look at this structure of of saying it's a 135 which i usually still do right we want to say this is the the d i don't want to name it in relative terms to the c i want to name it in relative terms to the d as the starting point down here i'm not doing that right where we're all these intervals are in relation to this c not to the d and that makes sense to one at one perspective because we're basically looking we're looking at everything as though it has been constructed from the c so what if we wanted to look at how this was constructed in relation to what does it mean to be the 135 in relation to the starting note of a d then we can look at the relative scale and make it the one basically and it's going to construct we can see a minor three chord if we did a just three notes but if we get more detailed and go out to the to the five this the seven the eleven and thirteen and so on so we could go to the relative minor in other words and you get basically the minor of those three notes and you can get the intervals for the minor but if you're looking at the whole everything that could be constructed from a c you will get a minor construction when you look at the intervals for the first three notes but you'll actually you're actually looking at a dorian construction if you go on beyond those three notes so the next thing we'll want to do then is map this thing out again with the related modes the complement modes which will be like the dorian for example which will then have the dorian as the one so that we can then see these intervals the one three five for example that will create the minor chord that's also within a dorian or the or the d minor right if we went to the the a minor down here for example which is might be the easiest one to think about we would then create the minor or a olean worksheet where this would be the one and then this would create the minor the minor intervals not relating them to the c but now relating them to the related a right and so that's how everything is basically relative that's why it gets kind of confusing because we constructed all of these scales from the key of c these are all the scales that can be constructed from the key of c by using just our normal routine of taking all of the notes in the key of c and then just skipping every other note to build to build the chords and then we know what those chords are by the intervals right so so we know the chords by the intervals the distances between the the notes and then to see those intervals formally we can basically make these other worksheets in the related and the related modes such as the a minor mode making the a the one and then looking at all the related notes to it to that one so the bottom line i'm trying to get to here is when you look at this worksheet the intervals for the one chord makes sense right because everything is tied out to the one chord but when you look at the two you can still see these intervals as they are related to the construction in the c but that's not how most people talk about it right they're going to talk about it as though it's related to its related scale the d minor most people will think of it as but it's really the d dorian if it's the two chord which will come up with a minor chord uh so so when so we're rethinking the intervals as the one three five so again we could look at the related dorian worksheet which will just reshuffle these from a different starting point to a relative different positions so that the intervals will now be in relation to the d okay so we'll talk about that more in future presentations