Pay Scale Analysis. RANKINGS, XLOOKUPS, IFS or all of the above nested!?? Please Help!

[Emaus_Sedan]

Hi. I am newish to data analysis (and forums) and am familiar with IFS and XLOOKUPS and have used RANKING in the past (very hazy on it’s use now something inside is saying might be useful!). Being brief I have been given a side task to solve by our HR team regarding pay scale mapping and ongoing cost for employees. I have done the majority of what I think would be useful for the task but am stuck on how to pull through changes in pay over 3 years depending on where the employee in question is on the pay scale and pay difference as they advance each year till they reach the top. The end result using mock data in a simple model would be for example:

Service	EmpID	Pay	Grade	Spinal Point	Year 1​	Year 2	Year 3	NOTES
A	E101	£22,800​	Grade C	5​	£411.33​	0​	0​	2 spinal points
B	E104	£24,054​	Grade D	7​	£431.54​	£441.00​	0​	4 potential spinal points
C	E106	£23,211​	Grade C	6​	0​	0​	0​	Top of spinal point

Based on:

Salary	Spinal Column Points	Grade
£21,230.54​	1​	Grade A
£21,612.00​	2​	Grade A
£22,001.72​	3​	Grade B
£22,397.75​	4​	Grade B
£22,800.00​	5​	Grade C
£23,211.33​	6​	Grade C
£23,628.00​	7​	Grade D
£24,054.46​	8​	Grade D
£24,486.00​	9​	Grade D
£24,927.00​	10​	Grade D

I have used power query to combine and create a master sheet as there will be data coming in from various services with various numbers of employees. I am not 100% sure if what I am doing is the way to go but I am trying to think out some logic as to what might be useful to help me achieve the end result so have created these tables to help. I think I am almost there but I am finding that I keep creating new tables or am adding columns that almost get there! There must be an easier way but simply put I just don’t know it! I think RANKINGS and XLOOKUPS and IFS are possibly the key… nested maybe but not sure how to achieve a formula to get me the difference between current salary and following years salary based on the Grade and Spinal point. Any directions on this would be greatly appreciated. I love challenges but this is giving me a headache!

I have created this chart to help with maxi,um number of spinal points put into the columns:

Pay scales (for most council staff)

GRADE	Spinal Points 1	Spinal Points 2	Spinal Points 3	Spinal Points 4	Spinal Points 5	Min	Max	Total Spinal Points in Grade
Grade A	1​	2​				1​	2​	2​
Grade B	3​	4​				3​	4​	2​
Grade C	5​	6​				5​	6​	2​
Grade D	7​	8​	9​	10​		7​	10​	4​

My Current sheet is looking like below but I am now stuck as not sure where to go regarding an actual formula to determine salary change over years.

Pay scales (for most council staff)

EmpID	Pay	Grade (Lookup)	Spinal Point (Lookup)	Lowest Spinal Point (Lookup)	Highest Spinal Pint for Grade (Lookup)	Calculation (IFS)	Gathering data for Year 1 (IFS)	Year 1	Year 2	Year 3
E101	£22,800	Grade C	5​	5​	6​	bottom	5
E102	£23,211	Grade C	6​	5​	6​	0	0
E103	£23,628	Grade D	7​	7​	10​	bottom	7
E104	£22,398	Grade B	4​	3​	4​	0	0
E105	£22,800	Grade C	5​	5​	6​	bottom	5
E106	£23,211	Grade C	6​	5​	6​	0	0
E107	£23,628	Grade D	7​	7​	10​	bottom	7
E108	£24,054	Grade D	8​	7​	10​	#N/A	#N/A

Sorry bit messy but hopefully you get the jist. Can elaborate where needs be or provide mock data and current results if needed!

Thanks.