Home |
Search |
Today's Posts |
#1
|
|||
|
|||
function needed
OK, i have tried at this for hours and got know where.
i have set up a simple worksheet for my businesses bonuses and does the workings out for each employee etc. It is split up into month by month. So say January there is £20,000 then the bonus will be 1.6% of that amount and seperated between 4 people. However, the £20,000 could also be a -£20,000. Now if it is -£20,000 i need to add it to the following months total. It is is not a negative amount then they get the bonus. Hope you can help. david |
#2
|
|||
|
|||
David Harrison wrote:
So say January there is =A320,000 then the bonus will be 1.6% of that amount and seperated between 4 people. So I assume that ostensibly, your bonus cell (e.g. C2) would have a formula like: =3DROUND(1.6%*B2, 0) where B2 is the current month's profit. However, the =A320,000 could also be a -=A320,000. Now if it is -=A320,000 i need to add it to the following months total. It is is not a negative amount then they get the bonus. Based on my assumption above, I believe the following formula might work for you (e.g. in C2): =3DROUND(1.6%*IF(B1 < 0, MAX(0,B1+B2), MAX(0,B2)), 0) where B1 is the previous month's profit. In the normal case where the previous month's profit (B1) was positive, the current month's bonus will be 1.6% of the current month's profit (B2), if positive, or zero. Thus, the bonus is zero when the current month's profit is negative (loss). In the case where the previous month's profit (B1) was negative (loss), the current month's bonus will be 1.6% of the current month's profit reduced by the previous month's loss, if the sum is positive, or zero. Thus, the bonus is zero when the current month's profit is (also) negative (loss). I think that is what you wanted. But I admit that I am reading between the lines. Your requirements were not entirely clear to me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function help needed | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
Urgent help needed: IF function | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions |