function needed

[David Harrison]

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

 

[joeu2004]

So say January there is £20,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:

=ROUND(1.6%*B2, 0)

where B2 is the current month's profit.

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.

Based on my assumption above, I believe the following formula
might work for you (e.g. in C2):

=ROUND(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.