Salary increase per team across year

[HD1080]

Hi all,

I had some brilliant help previously on an issue counting teams and salary per month over a set period of years.

I need to show a way of counting the team cost per month taking in to account salary increases, the way I'm doing this currently is to have an end date placed as if they've left the business on their last salary then a new start date placed for when they start their new salary. In reality this is not a great way to do this as if i need to look at the raw data there will be duplicate names and a minefield trying to figure out actual start dates and end dates.

What I've done now is create new columns with the increase which is actually their total salary increase rather than the amount (Maybe the amount would make more sense rather than to divide it per month) then the to and from dates, blank end dates mean they're still employed.

So as an example The team cost from Jan to April would be 1,500 but would go up to 1650 from May. Another issue I'm facing is because a salary increase could start mid month or first week of the month i wouldn't want it to count in both months.

I've attached an example workbook, if thought about doing if statements and vlookups but it won't work the way i need it to.

Thanks,

[Limor_OP]

So as an example The team cost from Jan to April would be 1,500 but would go up to 1650 from May

Hi,

You mean Team 3? why 1650? please explain your calculation.

[HD1080]

Yes sorry, Team 3.

The calculation is that from Jan - April Simon was on £1500 per month, this then increased to £1583.33 per month bringing the teams total cost up in May to £1750 and would continue to be that amount for the rest of the year until there is another increase, let's say as an example in November where it would continue to show the new increased amount per month. Although there's only one person in team 3 the same logic would apply for Teams with larger members some would increase others would not.

[Limor_OP]

so it should be 1650 or 1750?In your initial post you wrote 1650

[HD1080]

1750, i'd added the wrong amount to my example when explaining in my last post. It would be 1750 as he's gone from 18000 per year to 21000, meaning his monthly was 1500 but is now 1750.

[Limor_OP]

Hi
How about using this array formula in C16 and across?

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[HD1080]

Thanks for that, i've noticed it reverts back to 1500 from January 2020 but then in May 2020 picks back up to 1750.

Also there will be future increases to be accounted for, which would make the increase to date important, such as L3, as a test I put 25000 in M3 and it changed the values from May onwards to 2083 rather than the date I entered which was 05/09/2019. Any ideas?

I was thinking perhaps another way to do this would be to entered the increase amount divided by 12 and then add that on per month somehow?

[HD1080]

I had an idea about this but need to figure out a way to do it. Would it be possible to have as a list of columns each employees name from their start date to end date then their new salary as the same name etc? It can be summed up and also work as a pivot table if that makes sense?

[oeldere]

Crosspost

3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked. (A, Z*)

https://www.mrexcel.com/board/thread.../#post-5520057

[Limor_OP]

Hi,
Look at seet "Suggestion" in the attached.
I would open a column for each salary month and list the raises below that table, then apply this ARRAY formula to take raises for the relevant month or leave the salary similar to previous one:

Please Login or Register  to view this content.

Then calculate the cost in a seperate table using sumif:

=SUMIF($A$2:$A$10,$A20,G$2:G$10)/12