Tricky SUMIF (?) question

Mudface

MrExcel MVP
Getting really tangled with this one. Rather ominously I've given a 'redundancy pay' spreadsheet which needs a formula to calculate the number of weeks pay due, based on age and length of service. I have the ages (18-63) in cells B3:B48 and the years of service (2-20) in cells C2:U2. I need to calculate the weeks due as follows: -

1. 1 weeks pay for each year served to age 21
2. 2 weeks pay for each year served between 22 and 40
3. 2.5 weeks pay for each year served age 41 and over

I also need it to return a blank if the age minus the number of years served comes to less than the legal starting age of 16 (eg a person aged 22 cannot have served more than 6 years).

Thanks for any help with this, I've really tied myself in knots with this this morning :wink:.

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
On 2002-08-21 05:13, Mudface wrote:
Getting really tangled with this one. Rather ominously I've given a 'redundancy pay' spreadsheet which needs a formula to calculate the number of weeks pay due, based on age and length of service. I have the ages (18-63) in cells B3:B48 and the years of service (2-20) in cells C2:U2. I need to calculate the weeks due as follows: -

1. 1 weeks pay for each year served to age 21
2. 2 weeks pay for each year served between 22 and 40
3. 2.5 weeks pay for each year served age 41 and over

I also need it to return a blank if the age minus the number of years served comes to less than the legal starting age of 16 (eg a person aged 22 cannot have served more than 6 years).

Thanks for any help with this, I've really tied myself in knots with this this morning :wink:.

Hello,

I think this formula will do:
=IF(\$B3-C\$2<16;"";IF(\$B3<22;1;IF(\$B3<41;2;2,5)))

No, sorry, that won't work, I need to incorporate a multiplier for the number of years served.

On 2002-08-21 05:13, Mudface wrote:
Getting really tangled with this one. Rather ominously I've given a 'redundancy pay' spreadsheet which needs a formula to calculate the number of weeks pay due, based on age and length of service. I have the ages (18-63) in cells B3:B48 and the years of service (2-20) in cells C2:U2. I need to calculate the weeks due as follows: -

1. 1 weeks pay for each year served to age 21
2. 2 weeks pay for each year served between 22 and 40
3. 2.5 weeks pay for each year served age 41 and over

I also need it to return a blank if the age minus the number of years served comes to less than the legal starting age of 16 (eg a person aged 22 cannot have served more than 6 years).

Thanks for any help with this, I've really tied myself in knots with this this morning :wink:.

With your key information in a range of cells, I used named ranges

consider the following formulas for 2 of the age categories

=(Start<Age_21)*(MIN(INT(YEARFRAC(Start,Age_21)),6)*1)

=(INT(YEARFRAC(Start,Age41))-INT(YEARFRAC(Start,Age_21)))*2

You can extend and/or modify formulas like the above to calculate your amounts.

HTH Dave
This message was edited by Dave Patton on 2002-08-21 06:22

Thanks, Dave, but I need to do this in one really. Basically, given the current age of an employee and the no of years service, I need a formula which says something like

Weeks pay due= (No of years served<=21)+2(No of years served between age 22 to 40)+2.5(No of years served age 41 and above)

Edit for example a 35 year old has 19 years of service- weeks pay due = 6 (years under 22) + 13*2 (years between 22 and 40) = 33.
This message was edited by Mudface on 2002-08-21 07:27

On 2002-08-21 07:23, Mudface wrote:
Thanks, Dave, but I need to do this in one really. Basically, given the current age of an employee and the no of years service, I need a formula which says something like

Weeks pay due= (No of years served<=21)+2(No of years served between age 22 to 40)+2.5(No of years served age 41 and above)

Edit for example a 35 year old has 19 years of service- weeks pay due = 6 (years under 22) + 13*2 (years between 22 and 40) = 33.
This message was edited by Mudface on 2002-08-21 07:27

Hi Mudface,

I'd like to thank you for destroying my night (and mind).
Why you couldn't use the lovely table provided from any tax office is beyond me.

It's not the nicest formula, but PLEASE tell me that this works:

=(C\$1<\$B2-15)*SUM(IF(VLOOKUP(22-(\$B2-C\$1),{-1000,0;0,0;1,1},2,1)*(22-(\$B2-C\$1))<=C\$1,VLOOKUP(22-(\$B2-C\$1),{-1000,0;0,0;1,1},2,1)*(22-(\$B2-C\$1)),C\$1),IF(VLOOKUP(\$B2-22,{-1000,0;0,0;1,1},2,1)*(\$B2-22)<=C\$1,VLOOKUP(\$B2-22,{-1000,0;0,0;1,1},2,1)*(\$B2-22),IF(\$B2>=41,0,C\$1))*2,IF(VLOOKUP(C\$1-(\$B2-41),{-1000,0;0,0;1,1},2,1)*(C\$1-(\$B2-41))<=C\$1,VLOOKUP(C\$1-(\$B2-41),{-1000,0;0,0;1,1},2,1)*(C\$1-(\$B2-41)),0)*2,IF(VLOOKUP((\$B2-41),{-1000,0;0,0;1,1},2,1)*(\$B2-41)<=C\$1,VLOOKUP((\$B2-41),{-1000,0;0,0;1,1},2,1)*(\$B2-41),C\$1)*2.5)

To be placed in C2 from your e.g. above.

by the way this took 6 hours of head/wall/bang stuff.

If the numbers are wrong, figure it out for yourself (only joke let me know).

Kind regards,

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-08-21 16:30

P.S. Chris Davison recomended I look at it, so really, Chris, you have to STOP!

As it happens, I'm holiday 'til Tuesday now.

I wanna know why Chris D did not do this hinself, nothing complex or complicated there that i can see couple of vlookup and so on, what the issue Chris!

PS nothing taken away from Ian, might fine work! Very imprssive!! Im just digging my buddy Chris, but you all guessed that already!!!!

Chris deserves it, his formula give me a head ace !!!! But all i can do is thank him in advance the next time im stuck!!!

Hi Mudface,

If Ian's formula doesn't work (unlikely after all that effort! ) I have a question - you say lenght of service is indicated in the c:u columns - how? Text / number entry in the appropriate column? Only one entry per row?

Hi Mudface,

If Ian's formula doesn't work (unlikely after all that effort! ) I have a question - you say lenght of service is indicated in the c:u columns - how? Text / number entry in the appropriate column? Only one entry per row?

I can send the book if need be (just don't push it OK ).
They are numbers, the table looks like this:

B2:U18=

{0,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20;18,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;19,2,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;20,2,3,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;21,2,3,4,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0;22,2,3,4,5,6,0,0,0,0,0,0,0,0,0,0,0,0,0,0;23,3,4,5,6,7,8,0,0,0,0,0,0,0,0,0,0,0,0,0;24,4,5,6,7,8,9,10,0,0,0,0,0,0,0,0,0,0,0,0;25,4,6,7,8,9,10,11,12,0,0,0,0,0,0,0,0,0,0,0;26,4,6,8,9,10,11,12,13,14,0,0,0,0,0,0,0,0,0,0;27,4,6,8,10,11,12,13,14,15,16,0,0,0,0,0,0,0,0,0;28,4,6,8,10,12,13,14,15,16,17,18,0,0,0,0,0,0,0,0;29,4,6,8,10,12,14,15,16,17,18,19,20,0,0,0,0,0,0,0;30,4,6,8,10,12,14,16,17,18,19,20,21,22,0,0,0,0,0,0;31,4,6,8,10,12,14,16,18,19,20,21,22,23,24,0,0,0,0,0;32,4,6,8,10,12,14,16,18,20,21,22,23,24,25,26,0,0,0,0;33,4,6,8,10,12,14,16,18,20,22,23,24,25,26,27,28,0,0,0;34,4,6,8,10,12,14,16,18,20,22,24,25,26,27,28,29,30,0,0}

B19:U30=

{35,4,6,8,10,12,14,16,18,20,22,24,26,27,28,29,30,31,32,0;36,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30,31,32,33,34;37,4,6,8,10,12,14,16,18,20,22,24,26,28,30,31,32,33,34,35;38,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,33,34,35,36;39,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,35,36,37;40,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,37,38;41,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,76,79;42,4.5,6.5,8.5,10.5,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,80.5;43,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41;44,5,7.5,9.5,11.5,13.5,15.5,17.5,19.5,21.5,23.5,25.5,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5;45,5,7.5,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42;46,5,7.5,10,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5}

B31:U41=

{46,5,7.5,10,12.5,14.5,16.5,18.5,20.5,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5;47,5,7.5,10,12.5,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43;48,5,7.5,10,12.5,15,17.5,19.5,21.5,23.5,25.5,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5,43.5;49,5,7.5,10,12.5,15,17.5,20,22,24,26,28,30,32,34,36,38,40,42,44;50,5,7.5,10,12.5,15,17.5,20,22.5,24.5,26.5,28.5,30.5,32.5,34.5,36.5,38.5,40.5,42.5,44.5;51,5,7.5,10,12.5,15,17.5,20,22.5,25,27,29,31,33,35,37,39,41,43,45;52,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,29.5,31.5,33.5,35.5,37.5,39.5,41.5,43.5,45.5;53,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32,34,36,38,40,42,44,46;54,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,34.5,36.5,38.5,40.5,42.5,44.5,46.5;55,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37,39,41,43,45,47;56,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,39.5,41.5,43.5,45.5,47.5;57,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42,44,46,48}

B42:U52=

{58,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,44.5,46.5,48.5;59,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47,49;60,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,49.5;61,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;62,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;63,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;64,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;65,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;66,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;67,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50;68,5,7.5,10,12.5,15,17.5,20,22.5,25,27.5,30,32.5,35,37.5,40,42.5,45,47.5,50}.

Also, it is highly likely that the formula is wrong BUT itwil only be a Find/Replace on one of them that should getting working.

Mudface,

I've just taken this:

How much is a redundancy payment?

This depends on age and length of service and is calculated on the basis of complete years of service with the employer up to a maximum of 20 years.

The amount of the payment is:

one and a half weeks' pay for those years employed between the ages of 42 and 64
one weeks' pay for those years employed between the ages of 22 and 41
a half weeks' pay for those years employed between the ages of 18 and 21.

from UK law,

http://www.acas.org.uk/q_a/q_a9.html#2

so it looks like your company pay more than the legal requirement hence the reason for the table (IT HAD BETTER BE ).

Replies
1
Views
395
Replies
5
Views
965
Replies
3
Views
664
Replies
11
Views
410
Replies
13
Views
440

1,203,506
Messages
6,055,807
Members
444,826
Latest member
aggerdanny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back