# What excel formula could I use

#### hkese

##### New Member
 Profit/Loss HKD Bonus 10% Bonus Payable 633,122.15 63,312.21 63,312.21 2,409,094.43 240,909.44 240,909.44 133,312.37 13,331.24 13,331.24 362,337.61 36,233.76 36,233.76 (1,945,668.04) (194,566.80) 0.00 587,245.31 58,724.53 0.00 1,769,208.71 176,920.87 41,078.60

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Hello, I have a table as above.
I want to enter a formula into the "Bonus Payable" column so that I don't have to enter the number manually.
The amounts under Bonus Payable is calculated as follows.
If the profit is positive, then the bonus payable will simply be 10% of the profit.
If it's a loss, then there will be no bonus payable.
If next month has a profit, bonus will only be paid if 10% of it exceeds 10% of the loss incurred from previous month.
So 41,078.60 is calculated as (176,920.87 + 58,724.53 - 194,566.80) = 41,078.60.
Thanks v

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### turezky

##### New Member
For bonus payable for current month it is simply
Code:
``=IF([HKD]>=0; [HKD]*0.1;0)``
.

It's not really clear from your table which month is current, previous or next, but you can solve it with an additional IF statement

#### hkese

##### New Member
For bonus payable for current month it is simply
Code:
``=IF([HKD]>=0; [HKD]*0.1;0)``
.

It's not really clear from your table which month is current, previous or next, but you can solve it with an additional IF statement

The above formula would only work until there's a loss for the month.
As you can see, in row number 6, even though there's a profit, no bonus was distributed because 10% of it (58,724.53) did not exceed the loss from the previous month (-194,566.80).

##### Well-known Member
Perhaps try...
Excel 2010
ABC
1HKDBonus 10%Bonus Payable
2 £ 633,122 £ 63,312 £ 63,312
3 £ 2,409,094 £ 240,909 £ 240,909
4 £ 133,312 £ 13,331 £ 13,331
5 £ 362,338 £ 36,234 £ 36,234
6-£ 1,945,668 -£ 194,567 £ -
7 £ 587,245 £ 58,725 £ -
8 £ 1,769,209 £ 176,921 £ 41,079

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=MAX((SUM(\$A\$1:A2)*0.1)-SUM(\$C\$1:C1), 0)
C3=MAX((SUM(\$A\$1:A3)*0.1)-SUM(\$C\$1:C2), 0)
C4=MAX((SUM(\$A\$1:A4)*0.1)-SUM(\$C\$1:C3), 0)
C5=MAX((SUM(\$A\$1:A5)*0.1)-SUM(\$C\$1:C4), 0)
C6=MAX((SUM(\$A\$1:A6)*0.1)-SUM(\$C\$1:C5), 0)
C7=MAX((SUM(\$A\$1:A7)*0.1)-SUM(\$C\$1:C6), 0)
C8=MAX((SUM(\$A\$1:A8)*0.1)-SUM(\$C\$1:C7), 0)

</tbody>

<tbody>
</tbody>

/AJ

##### Well-known Member

NB Only works if the months remain in the correct order :S

/AJ

#### turezky

##### New Member
See possible solution below: The formula is as follows:
Code:
``=IF(A7>0,IF(A7+A6>=0,A7*0.1,0),0)``

#### hkese

##### New Member
NB Only works if the months remain in the correct order :S

/AJ

Perfect, problem solved.

##### Well-known Member
Awesome. Thanks for the feedback.

/AJ

Replies
3
Views
160
Replies
5
Views
123
Replies
1
Views
254
Replies
4
Views
66
Replies
0
Views
21