+ Reply to Thread
Results 1 to 18 of 18

Multiple nested if than statements thinking vba maybe direction to go

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Multiple nested if than statements thinking vba maybe direction to go

    I have hit a brick wall whether because I am rusty or simply cannot wrap my head around this. I am trying to build a excel workbook to calculate employee's merit increases to their annual income. To do so I must take both the COMPRATIO which is a figure provided, as well as their individual employee appraisal score use a matrix to come up with what percentage they will receive.
    The matrix looks like this
    PERFORMANCE RATING <91.01 >91.01AND<97.00 <97.01 AND <103 >103.01 AND <109 Greater than 109.00
    3.5-4.0 6.00% 5.00% 4.00% 3.00% 3.00%
    3.0-3.49 5.00% 4.00% 3.00% 3.00% 2.00%
    2.5-2.99 3.00% 3.00% 2.00% 1.00% 0.00%
    2.0-2.49 2.00% 1.00% 0.00% 0.00% 0.00%.

    I am not partial to how this is accomplished just that it is accomplished. Currently the work book is set up with a tab for all of the information called MASTERROSTER, and a tab called Merit Matrix that stores only the matrix. The cells holding their Comp Ratio is column X, the Performance Rating is in column O, and their Annual Rate is in column W all on the MASTERROSTER TAB. The end result of all the logic would place the amount in Column Q. I am totally fine with the logic not calculating the final amount as long as it spits out the percentage of the increase. The task that is murdering my brain is writing the 19 Plus If statements or how to write the VBA for it. I am an access guy who is being forced to use VBA in excel something I haven't done in probably 10 years. Please Help!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    You can use INDEX/MATCH/MATCH to return the value at the intersection.
    Post some sample data in a workbook, including the performance rating matrix above and we can show you how.

    BSB

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    In the attached is a quick example of how to use INDEX/MATCH/MATCH for this purpose.
    You should be able to amend this to fit your data.

    BSB
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Here is kind of a dumbie version of the much larger workbook I am working on. Master roster is not only used to input raw data but also to display the calculated data. The Merit percentages (Sheet1.Column M) are calculate via using the Merit Matrix and COMPRATIO(Sheet1.Column U) and Appraisal score (sheet1.Column K) to plug the percentage into Sheet1.column M. PFP Percentage is a bit more difficult the exempt matrix is for all salary labeled employees (Sheet1.Column Q) and uses the goal score (Sheet1.ColumnL) and plugs into sheet1.columnN. While the nonexempt matrix is for hourly employees who do not have job titles containing the "MSS" or "MSR" labels. I am sure I am making this more difficult in my own head then I need to be.Master PFP Merit List.xlsx

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Hopefully the attached is somewhere near what you need, although it may need a little tinkering if I've misunderstood your need.

    Anything I've added is highlighted in green. I've had to reverse the order of your matrices in order to make this work as INDEX with non-exact match required them to be in a specific order.

    BSB
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    The Merit calculations and the PFP calculations for the exempt works perfectly. I do have a question or two for both my own knowledge and for further understanding. What is the difference between using the IFERROR with the index and matching as opposed to just using the index and matching for the Merit calculations? For the PFP Non-exempt is there a way to only calculate PFP for employees who show as hourly in column Q, and if their job title in column C does not contain "MSS" or MSR?

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Quote Originally Posted by clpickett3 View Post
    What is the difference between using the IFERROR with the index and matching as opposed to just using the index and matching for the Merit calculations?
    All IFERROR is doing here is leaving the cell blank if an error occurs. For example, in row 2 there is no value in column K. This results in an error as the INDEX/MATCH/MATCH needs two numbers to find the intersection in the matrix.

    Quote Originally Posted by clpickett3 View Post
    For the PFP Non-exempt is there a way to only calculate PFP for employees who show as hourly in column Q, and if their job title in column C does not contain "MSS" or MSR?
    What do you want the formula to do if they're Hourly and DO have MSS or MSR in their job title?

    BSB

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Is this what you need?

    BSB
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    If they are hourly and do have the MSS or MSR in their job title then nothing needs to be done as they do not receive the bonus being calculated.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Quote Originally Posted by clpickett3 View Post
    If they are hourly and do have the MSS or MSR in their job title then nothing needs to be done as they do not receive the bonus being calculated.
    Which is what the file in post #8 does(?)

    BSB

  11. #11
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Im blind, thank you so much for your help and the information to educate myself

  12. #12
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    No problem. Happy to help

    Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

    BSB

  13. #13
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Ok I messed something up here. the merit increase formula works perfectly(column y) the PFP formula is creating NAs where good data should be. TEST.xlsx

  14. #14
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    The last INDEX/MATCH in your workbook is looking at column L rather than X. Change it as per the red highlight below and see if that works for you.

    This is the formula from row 5. The first one showing an N/A.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BSB

  15. #15
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Awesomesauce how do I mark it solved?

  16. #16
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    found another problem its not a huge problem but one individual instead of it plugging in a zero its causing an error thus a blank space. the persons column l is a 99.60 and the person's appraisal score is a 1.92. what would cause the error rather than the zero?

  17. #17
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,147

    Re: Multiple nested if than statements thinking vba maybe direction to go

    Midnight here so I've just shut down my laptop. Responding from iPad so cannot open Excel docs.

    Try stepping through the formula using the evaluate tool built into Excel. That will show you where it errors.

    I shall give it another look in the morning.

    BSB

  18. #18
    Registered User
    Join Date
    03-02-2015
    Location
    ridgecrest, ca
    MS-Off Ver
    2010
    Posts
    54

    Re: Multiple nested if than statements thinking vba maybe direction to go

    I will try thank you for everything

+ 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. multiple/nested if then statements
    By StrugglewExcel in forum Excel General
    Replies: 5
    Last Post: 09-14-2015, 02:52 AM
  2. [SOLVED] Nested if statements containing multiple and statements
    By John M. in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2014, 11:48 PM
  3. Multiple Nested IF Statements
    By rlsublime in forum Excel General
    Replies: 1
    Last Post: 11-15-2012, 02:01 PM
  4. Multiple nested IF statements and AND statements
    By TonyGetz in forum Excel General
    Replies: 2
    Last Post: 12-14-2010, 03:07 AM
  5. Replies: 12
    Last Post: 05-15-2009, 08:38 AM
  6. Multiple conditions in nested IF statements
    By havfunonline2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2009, 05:36 AM
  7. [SOLVED] multiple nested IF statements
    By jazzydwit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-29-2005, 01:30 PM

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