# Excel 2010: One off Bonus Paymets Depending on Production

1. ## 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. ## 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

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. ## 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. ## Re: Excel 2010: One off Bonus Paymets Depending on Production

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

6. ## 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)

#### Thread Information

##### Users Browsing this Thread

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

#### 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