• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Putting length of service into age brackets

Brooksy1

New Member
Hi all,

I am trying to work out how to calculate length of service into different age brackets. I have attached a simple excel example to hopefully help explain.

What I need is a calculation that breaks the length of service into the following age brackets

18-21 years
22-40 years
41+ years

This should be capped at a maximum of 20 years working down from the age on leaving.

For example for someone who on leaving is 45 years old and has 25 years service their breakdown would be

18-21 years 0
22-40 years 16
41+ years 4

I have been trying to come up with some sort of calculation but this is above my ability and haven't got close. It is also quite tricky to explain like this so feel free to tell me if this makes no sense.

Many thanks to anyone that can help

Tom
 

Attachments

  • Age calcs.xlsx
    9.2 KB · Views: 6
Another look at a solution. I've added a name selection and some other things. You can change the age ranges by altering the yellow values. You also need to make sure the ages match for the start/stop of the different blocks. Otherwise, you lose years in those gaps.
 

Attachments

  • Age calcs V1.xlsx
    11.7 KB · Views: 12
Last edited:
Morning both,

Sorry for the slow reply, i have been celebrating my 30th birthday.

Thank you for the above, both look quite advanced (in comparison to my knowledge level) so i will take some time to figure out how they work and then attempt to implement them into my workbook. I will come back to you hopefully with my success at getting it into my workbook.

Many thanks again

Tom
 
Another look at a solution. I've added a name selection and some other things. You can change the age ranges by altering the yellow values. You also need to make sure the ages match for the start/stop of the different blocks. Otherwise, you lose years in those gaps.

Hi Mike,

After playing around (and learning lots) with your version I have managed to get your solution working well on the workbook i am using, many thanks for that. Could you support me in getting the calculation to cap at 20 years service. I have attached your original workbook with a manually typed version in blue. This blue version shows how i would need the service capped at 20 years in total for Joe Bloggs working down from the highest years.

Hopefully this makes sense but please let me know if i haven't explained myself very well.

Many thanks for your help again.

Tom
 

Attachments

  • Age calcs.xlsx
    12.8 KB · Views: 5
Hi Mike,

No worries with the delay and thanks for all your help with this. It works beautifully and does exactly what i needed.

Many thanks again for all of your support.

Tom
 
Back
Top