+ Reply to Thread
Results 1 to 13 of 13

Macro entering new values as the limit of given range capacity is reached

  1. #1
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Smile Macro entering new values as the limit of given range capacity is reached

    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!
    Attached Files Attached Files
    Last edited by Lena1234; 08-07-2020 at 04:31 AM.

  2. #2
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi,

    If there's anything unclear please let me know!
    Or just please give me any feedback, if that's even possible what I'm asking for.

    I'd appreciate it!

    Thanks
    Lena

  3. #3
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Macro entering new values as the limit of given range capacity is reached

    This project seems to be a major undertaking, so it is going to take someone a lot of time. There may be folks willing to do the work, nay probably many who would love the challenge. My guess is you are not getting any response because of the size of work involved and folks are working on it but it will take time for an affirmative response. I would suggest daily reminders if you don't receive an update.

    One clarification, you want a recursive review of salaries to achieve a normalized distribution of said salaries, accounting for experience. This sounds lie a calculus problem, I just don'w know how to set it up differently.

  4. #4
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    Ok, thanks for your reply, maniacb.
    I'll remind you all about myself every other day then
    Perhaps there is somebody there up for the challenge.

    In terms of the way how to solve it - I thought that since I was able to make a formula with multiple IF functions (so pretty poor solution, but my work Excel doesn't operate on IFS), then it should be reasonably easy to do it in VBA.
    Actually I didn't want to review the salaries based on employees' experience per se - that would be ideal but seems really hard to do.
    We are redefining all the career steps, so it doesn't really matter if e.g. we're talking about a junior production assembler or senior production assembler.
    I just need them sorted by their salaries like below:

    ID Name Original data Changed data
    A XYZ 2800 3000
    B XYZ 2900 3000
    C XYZ 3000 3300
    D SENIOR XYZ 3100 3300
    E XYZ 3220 3300
    F XYZ 3350 3350
    G XYZ 3600 3600
    H SENIOR XYZ 3900 3900

    And as you can see here, the first range for this position ("production assembler", so all juniors, regulars and seniors) has the capacity of 2 employees, so that's why their new salary is 3000.
    The second range has the capacity of 4 employees, so the next 4 are between 3300 and 3600 etc.

    So basically:
    1. sort the salaries
    2. check if the salary is below the first threshold
    3. if so, change it to the first threshold as long as you are withing the agreed capacity
    4. if not, change it to the next threshold
    5. but if the capacity per threshold is not maxed out just yet and the employee is over the threshold, leave it as it is

    It's just a very general tool to assess the mass change - it's not an individual analysis.
    It's a tool to check how much the new minimal salary will impact the salaries.

    Hope that clarifies it! And hope that it answered your question...
    Last edited by Lena1234; 08-07-2020 at 05:02 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Macro entering new values as the limit of given range capacity is reached

    Given the fact that this is a brief to create a tool for you rather than a request for help to build it yourself, I would suggest that you buy some credit and post in the Commercial Services section, where you offer payment to someone to do the job for you. Let me know if you want to do this and I'll close this thread for you. If not, then I suspect that very few people will be prepared to do this amount of work for you for free.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    AliGW, thanks but I won't be able to buy a tool, that's why I headed to this section.
    But if this case is really difficult, I guess I'll just create 120 files (each per a job position) and use the formula
    But before I do something as sad as this I wanted to explore every option.
    Still hope there is somebody who figures us the solution in an easy way

  7. #7
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi Lena

    I am looking at this - will post back later today

    zeddy

  8. #8
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    Oh God, thanks zzzeddy!
    So happy to hear this. Thanks for attempting to do it!

  9. #9
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi Lena

    Well here's my first attempt - trying to work out what you want.

    I have added to your sample data, and changed the layout a bit to help explain what I'm doing.
    We can always adjust it to fit your requirements.

    Starting on sheet [rules] I have added what I call 'Position Levels'.
    You can enter how many persons 'occupy' each 'Position Level'.

    In my example attached, I have 27 incumbents distributed over 7 'Position Levels'.
    Each 'Position Level' has a min and a max salary.

    On the sheet [database], I have listed these 27 incumbents starting from the first Position Level.

    I have used formulas to 'fetch' corresponding data from the sheet [rules]

    My understanding is that on sheet [database], if the Original data value is less than the corresponding Minimum, then make the Changed data equal to the minimum value.

    If the Original data value is already above the corresponding Minimum, then leave it as it is i.e. the Changed data will be the same value as the Original data.

    I have added some Totals to compare the total for the Original data, and the total for the Changed data.
    I'm assuming you are interested in the effect/cost of new min/max pay bands???

    From my view, this means that the actual Position Max value does not get used.
    Now, if I have completely misunderstood what you are asking for, just let me know, and I will have another go!

    zeddy
    Excel Peanut Vendor
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi Zeddy,

    Thanks again for looking into it!
    So it turns out I explained it pretty poorly - I'm sorry!
    Please see the Excel attached.

    So after reviewing your file I can see that there are two goals:

    1. to create career steps visible in the payroll according to the model
    1.1 by model I mean my assumption that 25% of all employees per the core position is junior, 70% regular and 5% senior.
    1.2 core position - e.g. accountant, actual position - e.g. junior accountant, accountant, senior accountant
    1.3 each core position has its own 3 salary ranges, but people aren't assigned to any pay range - the pay range that will be used for them is just a result of earlier steps, that is e.g. reaching the limit of other people in the first pay range.
    1.4 here I think it's important to highlight that's this is just a model, so I'm fully aware that this is not fair that the 3rd person in the row will get a higher salary just because they are 3rd in the row, but the model needs simplifications in order to get an idea of the impact on the budget.
    1.5 thanks to that I can change the model (25/70/5) to e.g. 10/80/10 or 30/50/20 if I have or don't have enough money in my budget and see how will this change the total payroll
    2. to assess the influence of increasing by the government the minimum salary
    2.1 the pay ranges will change as the minimum salary changes - but that's outside of this tool (I can handle that)

    The result of this task is a tool in which I can set the new minimum salary, the salary ranges will change accordingly (outside of my request), and then the new salary ranges will be applied to the current payroll in a way that we can simulate creating career steps within a given core job.

    Hope that makes more sense now... and if not let me know, I'll prepare a more elaborate Excel
    Attached Files Attached Files
    Last edited by Lena1234; 08-07-2020 at 07:04 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi Lena

    Thanks for the extra info.
    We can now have another look..

    zeddy

  12. #12
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    17

    Re: Macro entering new values as the limit of given range capacity is reached

    Thanks Zeddy!

  13. #13
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Macro entering new values as the limit of given range capacity is reached

    Hi Lena

    Some further adjustments then, based on your updated sample file.

    Your calcs for the number of incumbents based on the percentage% allocated gives rise to numbers with decimals e.g. 5.6 etc . I am familiar with the concept of FTE ("Full-Time-Equivalent") for staff counts, so this may be OK for you.
    However, I am also a firm believer in "you can't sell half a handbag" so I have added a column F (model slots available) which deals with 'whole people'.

    I also tweaked a few formulas on the [database] sheet, see columns G, H, I

    My attached updated file is intended to show you some methods, rather than actually provide an exact model.
    But the more you explain to us, the more we can offer help.

    zeddy
    Excel Humane Resources Group
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Limit (working of) macro to a specified cell range
    By RandomVBA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2016, 07:46 AM
  2. [SOLVED] Applying a macro to a selected range.
    By roshanvmech in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2014, 09:34 AM
  3. [SOLVED] Applying If in macro to range of data
    By johnlynches in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2012, 04:13 PM
  4. applying macro code to a range of cells
    By just-gal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-31-2009, 02:26 PM
  5. Extending Combo Box capacity with an array from a range?
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2009, 10:08 PM
  6. Applying a macro over a range/selection
    By HydroXidE1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2009, 06:02 AM
  7. applying a macro for a specific field to a range of fields
    By Craig in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2005, 01:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1