Look up date and amount of last wage increase

[megera716]

I have a table of employees, their current wage and their historical wage with effective date. Our payroll system is....not great (for a multitude of reasons, which is why we're replacing it) and cannot report just the difference between their current wage and prior wage, so I know how much their last raise was.

I'm preparing raise budgets and I've got the most recent value for each employee (their current wage). I'm trying to look up each EE and return the wage they were at before their current one ("next-most recent") to find the difference between the two and the date of that raise.

See below for some anonymized data. I don't know why "Mickey" shows all these compensation "changes" when the rate isn't actually changing -- it's been $25/hour for the last 3 records (maybe there was some other variable that did change) -- but in Mickey's case, I'd want to return $3/hour (the difference between $22 and $25) and the date 1/1/2022, ignoring the 11/26/22. Daisy is pretty normal -- each date actually does show a change, but Minnie has not had a change at all (probably too new), so hers should return $0/"no change".

I sorted them in reverse chronological order but I can change that if needed.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). Recent version of Excel have some functions that would make this pretty easy, using the MAXIFS function.

See: MAXIFS function - Microsoft Support

 

[megera716]

Thank you @Joe4 ! I thought I did have that info on my profile but I have updated it to reflect that I am using 365 .

I haven't used MAXIFS before but I read the article you linked. It seems like this would be useful for returning the most recent date and/or wage but I'm trying to return the one before that.

And I don't even know if what I'm looking for CAN be done but I have hundreds of employees to do this for so the idea of doing the math one by one is....not really feasible. And if anyone would know how to do it, it would be the amazing folks here , who have helped me countless times (through my own posts or what I've gleaned from others and applied to my situation).

 

[Joe4]

In your example, the rates always seem to be increasing over time, as one would expect.
If that always is true, then you could use a nested MAXIFS, where you add a second condition, where we exclude the max date, and take the max of what is left.

So here is a simple example:

The formula in G2 is:

=MAXIFS(C$2:C$8,A$2:A$8,F2,B$2:B$8,"<>"&MAXIFS(B$2:B$8,A$2:A$8,F2))

 

[Fluff]

How about

=TOROW(TAKE(UNIQUE(FILTER(C2:C1000,A2:A1000=F2)),2))