Managing Department Salaries and Yearly Percent & Dollar Difference

[DaRTH KiRo]

Before I give up and just use a pivot table to do this. Is there a way, without a pivot table, to calculate the percent salary (column F) difference in column H and the hourly dollar (column G) difference in column I, for the previous year? I know I can do this with a pivot table by only using A:G and then using the pivot table to calculate differences; however, I would rather not have to look in two different places.

Below are columns E:I, columns A:D contain personnel information (column A contains employee name). Also, please ignore the two spots with salary and hourly, they are wrong and I was only using to try and test a few things.

Year	Salary	Hourly	% Change	Dollar Difference
2020​	$30,000.00​	$15.00​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2020​
2021​	$33,000.00​	$17.00​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​
2021​

 

[Toadstool]

Hi DaRTH KiRo,

Assuming the years are contiguous then this should work:

DaRTH KiRo.xlsx
	A	B	C	D	E	F
1	Name	Year	Salary	Hourly	% Change	Dollar Difference
2	Bob	2020	$30,000	$15.00
3	Bill	2020	$25,000	$12.50
4	Jim	2020	$22,000	$11.00
5	Sarah	2020	$15,000	$7.50
6	Vanessa	2020	$32,000	$16.00
7	Julie	2020	$29,999	$15.00
8	James	2020	$22,222	$11.11
9	Bob	2021	$33,000	$16.50	10.00%	$3,000
10	Bill	2021	$25,000	$12.50	0.00%	$0
11	Jim	2021	$20,000	$10.00	-9.09%	($2,000)
12	Sarah	2021	$30,000	$15.00	100.00%	$15,000
13	Vanessa	2021	$36,000	$18.00	12.50%	$4,000
14	Julie	2021	$30,500	$15.25	1.67%	$501
15	James	2021	$33,333	$16.67	50.00%	$11,111
16	Jim	2022	$22,000	$11.00	10.00%	$2,000
17	Sarah	2022	$32,000	$16.00	6.67%	$2,000
Sheet1

Cell Formulas
Range	Formula
E2:E17	E2	=IFERROR((C2-INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)))/INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)),"")
F2:F17	F2	=IFERROR(C2-INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)),"")

 

[DaRTH KiRo]

Hi DaRTH KiRo,

Assuming the years are contiguous then this should work:

DaRTH KiRo.xlsx
	A	B	C	D	E	F
1	Name	Year	Salary	Hourly	% Change	Dollar Difference
2	Bob	2020	$30,000	$15.00
3	Bill	2020	$25,000	$12.50
4	Jim	2020	$22,000	$11.00
5	Sarah	2020	$15,000	$7.50
6	Vanessa	2020	$32,000	$16.00
7	Julie	2020	$29,999	$15.00
8	James	2020	$22,222	$11.11
9	Bob	2021	$33,000	$16.50	10.00%	$3,000
10	Bill	2021	$25,000	$12.50	0.00%	$0
11	Jim	2021	$20,000	$10.00	-9.09%	($2,000)
12	Sarah	2021	$30,000	$15.00	100.00%	$15,000
13	Vanessa	2021	$36,000	$18.00	12.50%	$4,000
14	Julie	2021	$30,500	$15.25	1.67%	$501
15	James	2021	$33,333	$16.67	50.00%	$11,111
16	Jim	2022	$22,000	$11.00	10.00%	$2,000
17	Sarah	2022	$32,000	$16.00	6.67%	$2,000
Sheet1

Cell Formulas
Range	Formula
E2:E17	E2	=IFERROR((C2-INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)))/INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)),"")
F2:F17	F2	=IFERROR(C2-INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($A$2:$A$9999=A2)*($B$2:$B$9999=B2-1)),1)),"")

That was perfect, thank you! I couldn't get the year part to work out.