Emaus_Sedan
New Member
- Joined
- May 23, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
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:
Based on:
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)
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)
Sorry bit messy but hopefully you get the jist. Can elaborate where needs be or provide mock data and current results if needed!
Thanks.
| 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 |
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.
