# 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  Register To Reply

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  Register To Reply

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)  Register To Reply

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  Register To Reply

5. ## Re: Excel 2010: One off Bonus Paymets Depending on Production

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

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)  Register To Reply

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