 Hi guys, it's MJ and in this video, we're going to go through Excel and I'm going to show you how to Extract the cohorts. So let's say we have a life table Okay, we have a life table and it's got various years. So let's say 2000 2001 and 2002 and let's say we have our ages 20 21 22 23 and 24 and What we normally get in a live table is the amount of people alive You know some amount of people who are age 20 live at the date 2000 So what I'm going to do is I'm just going to put in some reference numbers. So normally you would actually see proper Proper values here like they're various likes like you can see lives don't normally increase But why I'm doing this is I'm going to show you 22 23 and let's make that 32 33 and then we're going to make this 42 43 and we're going to make this 52 53 okay, so this is our life table Okay, we've got a little life table and let's say we now want to get the cohorts Okay, so we want to get the cohorts For these various years, we can actually just copy paste this over here Now the cohorts are Quite a difficult thing to extract in Excel because the cohorts are It's when you you take an age group and you follow them throughout time So these 20 year olds over here are the 21 year olds in 2001. They are the 22 year olds in 2002 so kind of like They kind of like follow this this type of pattern So what we want to do is we want to extract the diagonals. So these guys over here would be a different Cohort and these guys over here would be just green would be a different cohort like that So this is the yellow one here How do we get these numbers over here? how do we get them going down and I mean, maybe we should We should have two thousand and three two thousand and four as well. I'm just have some more numbers 24 25 34 35 44 45 and then 54 55 okay, so we want to get We want to get These yellow lines over here now. I mean you could go and type them out I mean that is one possible way But when you're dealing with live tables, it's very likely you could have like 30 years over here You could have you know 80 ages so to do that It's gonna take up a lot of your time So is there a quick way that you can do it for for all of them? so well Fortunately, we do and that is going to be the point of this video So what I'm going to be using is something known as the index function Okay, so the index function We select an array actually before I do that a very good thing to do is if you're going to be using an array I like to give it a name. So let's call it data We call it data and then that way in the formula We don't have to put all those dollar signs and all that type of stuff with Excel so we just go index and Then we want data and then you can see we kind of want column 1 row 1 and Then we'd go index Okay, data and now we want row 2 row 2 Okay, and you can see we can start generating all of those But what we've seen is that there is a bit of a pattern each time they are increasing by 1 so what we can do is The following we can use our row information in order To Calculate these numbers over here. So what do I mean by that? So if I say we're dealing with age 20 years are smaller So if we go negative 19 plus And we go here. We want to fix it on B. We don't mind if it moves down the columns So we're gonna put the dollar sign there and then our column is we can also have say negative One what negative 1999 Plus oh So before we do that, let's just make it make it simple This is a little bit tricky But let's see if we can oh I'm sliding down Okay, let's see if this works. Okay, so we've got 11 and now we want to see oh Okay, that's great. So what you can see what we're doing here is we're using these values In order to be our index positions in order to return it In the array But the problem with this is that if I now drag across Okay, it doesn't work. Okay, you can see They're giving me the same results. So what I want to do We want to clear this over here. I want to use also my year So let's actually just take away the color so Over here. I want 11 But over here, I'm gonna want one two Okay, so We can't really use this formula here or we can if we just modify it slightly. So let's go 1999 plus 2000 okay now we want to fix it on the row So we'll put the dollar sign there. Okay Let's just see if this still works for us Okay, it doesn't that has missed everything up um How do we get around this Let's just see if we put a dollar sign there Oh, no, of course not because what it's going to be doing is We want the column to also change as well So we can't actually do that So What do we do? What do we do? What do we do? I should have prepared better for this video Um, but this is the whole thing you have to you have to figure things out As you go about them. So let's actually stick with our b15 And now let's actually Let's add something else as well. So let's make this minus 20 plus We want the 2000 Minus 199 Nine is that going to work? Let's see. Let's see if this is going to work. We want to fix the column Um, we want to fix the 14. That's what we want to fix Okay, so let's see if that works Okay, that has worked and because we can see all the yellow numbers Are over there. So we've got all our little yellow numbers Okay, now what I want to see I want to see one two I want to see two three. I want to see three four. I want to see four five Then I'm going to be a very happy chap and these should be Should give errors because they don't actually have the value. So they'll go ref Okay, let's see. Does this work? Fantastic fantastic. So we actually have figured this thing out um So actually let me just show you here with the orange. So these orange values here Are popping up over here And these yellow numbers over there Are popping up over there and then if we had made the same blue This is where the blue numbers would have been. So what we've done is we've extracted the cohorts um And how we've done it is we've used the index function. So the index function is very important We're always using the same Data, so I've given that a name reference and then what I've done is you could see it took a little bit of thinking But you had to kind of see the sequence what was what was being required And then adjust that sequence using either the the names of your columns Or the names of your rows And this is going to be different depending if you're starting at age 80 or if The the years or every five years or something like that you're going to have to figure that part out by yourself But with a little bit of practice and a little bit of trial and error You can see that we've able we've been able to extract the cohorts From our life table and this is going to save you a lot of time when like I said when it's like 50 years projected And yeah, something like 80 years. This is going to be a big time saver If you can work out this little formula there Anyway, that is the the end of the video. I hope you enjoyed it. I'll probably be making a few more Excel for actuary videos. So click subscribe and let me know your thoughts in the comment section below. Thanks guys. Cheers