Putting length of service into age brackets

[tbrooksy]

Hi All,

I have previously posted this on another forum, received some support but nothing for the last two weeks. I have seen that it can be frowned upon to ask the same question across forums so please let me know if this is against etiquette.

My original question asked

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


I received the attached 'first attempt' workbook which works beautifully at segregating the length of service into age brackets, however it does not cap the age at 20 years. The attached 'cap need' document shows what I would like it to calculate in the blue box.

I wonder if anyone is able to suggest some alterations to the formulas that will cap at 20 years working down from higher to lower ages?

Hopefully this makes sense but please let me know if you require any further info.

Many thanks

Tom

[Tsjallie]

I think you need to add MIN-function.

Please Login or Register  to view this content.

I put these formulas in P2:P4 of the sheet (attached)

[tbrooksy]

Hi Tsjallie,

Thanks for the quick response.

When i had the original response on the other forum i altered their formula to fit my workbook as mine does not use a table. I should have made this clear but had hoped i could do the same with any responses on here, apologies for that.

Below is an updated formula that fits my

Looking at yours i am unable to make it fit into the above however i am a noob to these sorts of formulas.

Is there a way i can adjust my formula above to work or will i need to introduce a table to my workbook?

Many thanks for your help

Tom

[Tsjallie]

i altered their formula to fit my workbook as mine does not use a table

Is it a completely different structure or just not a table?

...or will i need to introduce a table to my workbook?

You don't need to, but I always highly recommend the use of tables instead of ranges.

[Glenn Kennedy]

But if you must...

select the table, TABLE TOOLS/DESIGN/TOOLS/CONVERT TO RANGE

and the formulae change automatically. I'm not a huge fan of tables, personally...

[tbrooksy]

It is a similar structure, the main difference is some cells it references are on another sheet. Other changes are the age brackets run across columns rather the down a column.

I am quite new to the excel world and am at the stage where i can alter formulas and VBA code to help fit my needs for the most part but i have not looked into tables yet. I will do some reading and try out a few to see how they work.

Thanks for your help with this

Tom

[tbrooksy]

But if you must...

select the table, TABLE TOOLS/DESIGN/TOOLS/CONVERT TO RANGE

and the formulae change automatically. I'm not a huge fan of tables, personally...

Thanks Glenn,

Great bit of info that will no doubt help me out.

[tbrooksy]

Hi Tsjallie,

I have implemented your formulas and tried a few variants however the calculations don't work for all.

I have attached the original sheet with your formulas added. When you use the drop down box Joe Bloggs works fine however the other two are displayed oddly. I have inserted 2 red boxes which show what i would like to have as a result of the formulas.

Many thanks for your support.

Tom

[Tsjallie]

Hmm, think it's something with an ill reference. Will have a look at it.

[tbrooksy]

Thanks Tsjallie

[Tsjallie]

Ok, I think I got it now.
All three formulas now check if the age is valid for a bracket (PB and GR shouldn't have years in the 41+ bracket) and
they all also consider the LengthOfService now. So the result for a bracket will be the smallest of Bracket-years, (remaining) Service Years and the (remaining) limit of 20 years.

Please Login or Register  to view this content.

As you can see the formulas are now pretty lengthy and hard to read.
I think it would be good practice to define and use some names to make it more readable.
For example the 18-22 years formula could also look like this

Please Login or Register  to view this content.

[tbrooksy]

Thanks Tsjallie

I have implemented the above and tested it, the formulas work beautifully.

Thank you so much for your help, it is appreciated.

Tom

[Tsjallie]

Hi Tom,
good to hear that and thx for the rep!
If your problem is fully fixed pls mark the thread SOLVED (see Thread Tools above).