+ Reply to Thread
Results 1 to 6 of 6

Excel 2010: One off Bonus Paymets Depending on Production

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    3

    Excel 2010: One off Bonus Paymets Depending on Production

    Hello,

    This is my first ever Thread!

    I am having trouble working out the formula for the following problem:

    Cells A1:J1 list the aggregate amount of $ in a bank account over time A1=0, B1=100, C1=400, D1=500, E1=900, F1=1000, G1=1000, H1:J1=1500

    I am trying to produce a formula for A3:J3 which shows one off bonus payments as different target $ balances in the account are met. i.e $100, $500, $1000. Each one time bonus would be 1% of the target.

    The result would therefore be:

    A3=0, B3=1, C3=0, D3=5, E3=0, F3=10, G3=0 H3:J3=0

    What is tripping me up is that in E1, while the balance is greater than the target of $500 I DO NOT want the formula to pay 1% in E3 as that bonus was paid in D3 for D1.

    Any help would be greatly appreciated.

    Alex

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Excel 2010: One off Bonus Paymets Depending on Production

    not sure if this is what you want, but give it a try...

    =IF(OR(A1=1000,A1=500,A1=100),A1*0.01,0)
    copied across
    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
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel 2010: One off Bonus Paymets Depending on Production

    apologies i should have explained that the $ amounts in cells A1:J1 will not match the targets i.e. C1=492.58 D1=503.97 E1=953.83

    The formula in row 3 needs to recognise that D1 is over the threshold of 500 (as if E1 but no bonus payable for E1 as already paid for D1)

  4. #4
    Registered User
    Join Date
    01-17-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel 2010: One off Bonus Paymets Depending on Production

    Thank you for the effort but apologies i should have explained that the $ amounts in cells A1:J1 will not match the targets i.e. C1=492.58 D1=503.97 E1=953.83

    The formula in row 3 needs to recognise that D1 is over the threshold of 500 and therefore pay the bonus (E1 is also over the 500 threshold but no bonus payable for E1 as already paid for D1)

    Thanks

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Excel 2010: One off Bonus Paymets Depending on Production

    lol yup, thats what I figured. Give me a few minutes

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Excel 2010: One off Bonus Paymets Depending on Production

    ok try this, copied across....

    =IF(OR(AND(A1>100,A1<500), AND(A1>500,A1<1000), A1>1000),0,A1*0.01)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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