Hi Guys!
I need your help with a macro.
I wanted to create a spreadsheet that allows me to check different salary range scenarios.
The goal is to create a tool that automatically changes person's salary value as the number of employees per given range reaches the limit.
So for example, as the table below shows, in the first range there should be 2 employees. The salary is OK if it's between 3000 and 3299.
If the first two employees' salary is lower than 3000, it should be corrected to 3000.
However, if the actual (current) employee's salary is higher than the limits would suggest, the value should stay the same.
So if the second person earns 3300, then there will be only 1 person in the first range and that's OK.
Range name Max incumbents number Range min Range max Range1 2 3000 3299 Range2 4 3300 3599 Range3 2 3600 3899
I created a working function for this case in the workbook attached, so please feel free to check the formula if my explanation is not clear enough.
I need one sheet for assumptions (ranges per a given position) and one sheet for the database and scenario calculation in one.
The formula works well, but it's way too heavy to handle a couple of scenarios, n-hundreds employees and approx. 120 job positions.
That's why I'd like to use a macro to do it, but I'm not that fluent in VBA to do it on my own.
Perhaps one of you have an idea how to solve it?
I'd appreciate it a lot, 'budgeting season' is just around the corner and without this tool it will be hard to make any decent analysis.
Thank you!
Bookmarks