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.

    Yours,
    Chandoo
  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

    Hui...

  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

    Messages:
    23
    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

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Brooksy1
    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

    Messages:
    177
    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

    Messages:
    23
    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
  5. Brooksy1

    Brooksy1 New Member

    Messages:
    23
    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

    Attached Files:

  6. Mike86

    Mike86 Active Member

    Messages:
    177
    @Brooksy1

    Sorry for the response delay, I don't check here every day. Without getting too messy, the attached file seems to do what you want. Hiding Col J will make it look cleaner.

    Attached Files:

    Brooksy1 and Thomas Kuriakose like this.
  7. Brooksy1

    Brooksy1 New Member

    Messages:
    23
    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

Share This Page