Macro For Calculating The Projection Graduation Year Of A Student

[ModerCreed]

Morning guys,

I am doing a little bit of Excel work for an independent school's alumni Database, and excel is not my program of choice so I am coming to you guys with a question.

Put simply, each row represents a student, with all the columns carrying various info on that student.

I have a number of past students at the school (in this case, past students are students that didn't graduate from the school, given that they left for some reason) where we have the grade they left the school. We want to use that grade to calculate the theoretical year the student would have graduated high school (Essentially which grad class they would have belonged to if they stayed at the school)

I would like to write some sort of macro or create some formula to do this.

Put into pseudo code, this is what I want it to do:

- Look at the ending grade column (AA on my spreadsheet)

- Look at the ending year column (AB on my spreadsheet)

- Take the ending grade and add a year for every grade they completed after leaving the school before graduating in grade 12

- Put this year in column AD

Here's an example to make things clearer:

A student left the school in grade 9 in 1995. The macro/formula would then correctly calculate that their theoretical grad year is 1998.

Some notes:

- Where I am from, schools go from grade 1 to 12.

- The end year column is the latter part of the school year. So if a student left in the 1998-1999 school year their end year would be 1999.

I hope this isn't super vague! Can anyone help me with this?

 

[buxtongt]

You don't need a macro to do this, a formula in the cell you want to populate will do. Something like:

=AB1+(12-AA1)

should do the trick. Hope this helps.

 

[~Gretchen~]

I know this is old but just wanted to say THANKS! I needed to do this today and buxtongt's suggestion worked perfectly!

You don't need a macro to do this, a formula in the cell you want to populate will do. Something like:

=AB1+(12-AA1)

should do the trick. Hope this helps.