1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. 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


  3. 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

Discussion in 'Ask an Excel Question' started by Brooksy1, May 19, 2017.

  1. Brooksy1

    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


    Attached Files:

  2. vletm

    vletm Well-Known Member

    1st version ...
    Results are in yellow range
    Red numbers are Your variables.
    L-column has two help-values.

    Attached Files:

    Brooksy1 and Thomas Kuriakose like this.
  3. Mike86

    Mike86 Active Member

    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.

    Attached Files:

    Last edited: May 19, 2017
    Brooksy1 and Thomas Kuriakose like this.
  4. Brooksy1

    Brooksy1 New Member

    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

  5. Brooksy1

    Brooksy1 New Member

    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.


    Attached Files:

Share This Page