+ Reply to Thread
Results 1 to 4 of 4

Salary Calculation Question

  1. #1
    Registered User
    Join Date
    05-21-2010
    Location
    Jhb, South Africa
    MS-Off Ver
    Excel 2019
    Posts
    7

    Question Salary Calculation Question

    I have a problem. The problem is that we know the cost to company and we are trying to calculate medical aid contributions and basic salary. Would've been easy if the contribution was a fixed amount. Problem is that the contribution is on a sliding scale and there are different plans.

    Is there a way to solve this in Excel with just plain maths or formulas?

    The calculation would look like follows:
    Basic Salary x
    Medical Aid (based on x) y
    Cost to Company x+y

    And then medical aid extract:
    If medical aid option 1 is taken:
    x < 1000 y = 20
    1001 < x < 2000 y = 25
    2001 < x y = 30

    If medical aid option 2 is taken:
    x < 750 y = 18
    751 < x < 1800 y = 23
    1801 < x y = 25
    Life is what happens when you make other plans

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Salary Calculation Question

    create a table somewhere that looks like this...

    0 20 18
    750 20 23
    1000 25 23
    1800 25 25
    2000 30 25


    then use a VLOOKUP using the medical aid option to determine which column to return

    eg

    if
    A1 = Salary
    B1 = Option

    and youve named the table above "medicalaid" then you'd use

    Please Login or Register  to view this content.
    to return the value you want.
    If someone has helped you then please add to their Reputation

  3. #3
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Salary Calculation Question

    Hello Blu3,

    Welcome to the forum

    To shortly answer your question, yes, it is possible, and you can even put in the option number you want Excel to take. This can be done through purely Formula or VBA.

    For e.g, if Basic Salary is in Column A starting from A2, Medical Aid is in B starting from B2, Cost to company is simply C2: = A2 + B2.
    If Medical Option is in column D startin from D2 then

    B2: =if(D2=1,LOOKUP(A2,{0,1000,2000},{20,25,30}),LOOKUP(A2,{0,750,1800},{18,23,25}))

    Assuming the value in D2 can only be either 1 or 2, if there are more than 2 values acceptable for D2 you can include them by using nested IF

    If you can attach a small sample worksheet with dummy data, that'll be much easier for others to help you too
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  4. #4
    Registered User
    Join Date
    05-21-2010
    Location
    Jhb, South Africa
    MS-Off Ver
    Excel 2019
    Posts
    7

    Re: Salary Calculation Question

    Thank you for the replies and help guys.

    The problem that I had was a bit more complicated than just the two medical aid and I was trying to avoid using goal seek.
    I've attached my final spreadsheet, which has got a small goal seek VBA in, since we are trying to do the calculation for 5,000 employees.

    Hopefully this will help someone in the future.
    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. Provident fund calculation for salary computation
    By jvlkerala in forum Excel General
    Replies: 4
    Last Post: 06-25-2018, 11:13 AM
  2. Help in Calculation Salary
    By AVG123 in forum Excel General
    Replies: 12
    Last Post: 09-19-2015, 08:45 AM
  3. [SOLVED] Employee salary calculation help!
    By mhweiler in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-21-2015, 10:05 PM
  4. [SOLVED] Salary calculation by joining date
    By Thet4444 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-17-2014, 04:35 AM
  5. salary calculation
    By tariqnaz2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2013, 03:06 PM
  6. time shift and salary calculation
    By Lamb Chop in forum Excel General
    Replies: 1
    Last Post: 08-16-2006, 06:45 AM
  7. [SOLVED] How can I do an hourly salary calculation in Excel
    By AndyM in forum Excel General
    Replies: 2
    Last Post: 09-15-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