+ Reply to Thread
Results 1 to 5 of 5

Multiple Conditions - Bonus Salary Question

  1. #1
    Registered User
    Join Date
    07-05-2014
    Location
    Orlando, FL
    MS-Off Ver
    2010
    Posts
    31

    Multiple Conditions - Bonus Salary Question

    Hello guys,

    I've been tearing my hair out trying to figure out a solution and formula to this multiple condition problem:

    SA HIRE DATE SALES QUOTA

    BOB 1/1/2014 375,000 300,000
    MIKE 1/1/2014 475,000 350,000
    BILL 1/1/2014 275,000 150,000
    SUE 2/1/2014 350,000 225,000
    RODNEY 1/1/2014 350,000 225,000
    NICK 1/1/2014 475,000 400,000
    SARA 1/1/2014 275,000 200,000

    Conditions:

    1) $2k bonus for selling 50k above quota. Must sell a minimum of 300k
    2) $4k bonus for selling 100k above quota. Must sell a minimum of 400k
    3) Each rep is eligible to earn one bonus level
    4) For mid quarter hires the bonus payout will be prorated based on number of months worked. The sales above quota requirement and the minimum sales requirement should be prorated as well.

    How do you write the multiple condition formula to come up with a bonus answer in one column, is this eevn possible? Please help me!!!

  2. #2
    Registered User
    Join Date
    07-05-2014
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Multiple Conditions - Bonus Salary Question

    condition 1, 2 and 3 will be satisfied by following formula.
    for condition 4 can you explain in example.

    C3 = SALES
    C4 = QUOTA
    =IF(AND((C3>400000),(C3-D3>100000)),4000,IF(AND((C3>300000),(C3-D3>50000)),2000,0))

  3. #3
    Registered User
    Join Date
    07-05-2014
    Location
    Orlando, FL
    MS-Off Ver
    2010
    Posts
    31

    Re: Multiple Conditions - Bonus Salary Question

    Ahhhhh!!!! Thank you so much!! I can breathe now LOL!
    Last edited by henrock24; 07-05-2014 at 02:08 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,449

    Re: Multiple Conditions - Bonus Salary Question

    Can you please post your spread sheet? I can't follow what you are trying to do from post#1
    Glenn



  5. #5
    Registered User
    Join Date
    07-05-2014
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Multiple Conditions - Bonus Salary Question

    excel file is attached. explain the last condition with example.

    bonus.xlsx


    if it helps kindly add reputation.

+ 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. Replies: 2
    Last Post: 05-27-2014, 01:17 AM
  2. Forecast Bonus Calculator - Require a formula to calculate the bonus' due
    By The_Snook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 07:32 PM
  3. Anyone have any recommended Salary Bonus templates?
    By JPSIMMON in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-16-2013, 01:42 PM
  4. Replies: 8
    Last Post: 09-06-2013, 05:57 AM
  5. Multiple Conditions Question
    By RickT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2008, 12:01 PM

Tags for this Thread

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