my problem is that the plan bonus is hit for line 4,
and should never come up again because it is not possible
to get any bonus once it it attained.
If someone else can interpret your posting correctly and help you,
that's great.
I would like to help you, but I simply cannot understand your
posting. Perhaps some specifics will help you see what needs to be
clarified, at least for me.
The plan bonus should end once it equals the total growth bonus.
Here is the formula that I use to calculate the Plan bonus:
Is this the formula that is not working and you wish some help to
correct?
Or is this a working formula, and you want some help with crafting
another formula?
If the latter, what exactly do you want the latter formula to do?
=IF(H16>0,0,IF(SUM(F$16:$F17)>=$C$12,(SUM($G$16:$G$27)),0))
where H16=Plan bonus, sum(f16:f17)=increase over best,
C12= monthly plan, sum(g16:g27) =growth bonus
You said this formula computes the "plan bonus". But the formula
refers to H16, which you say is the "plan bonus".
Is this formula in H16, and it contains a circular reference? Or is
this formula in some other cell?
If this formula is in H16, did you enable circular referencing, i.e.
select Iteration under Tools > Options > Calculation)?
(I do not recommend that.)
Finally, what is the relationship between the range and cell
references in the above formula and the rows and columns in the table
of data that you provide?
Also, is "f16:f17" a typo, and it really should be F16:F27,
corresponding to G16:G27?
Returning to what you wrote most recently:
my problem is that the plan bonus is hit for line 4,
and should never come up again because it is not possible
to get any bonus once it it attained.
Perhaps the following answers your question. Read this with a grain
of salt, since I do not fully understand your requirements.
If the bonus is in G16, G17 etc, and C12 is the maximum total bonus,
perhaps you want the following formula in G17 and below (where F16 is
the greater of zero or the difference between sales and 2-month avg
sales):
=max(0,round(min(F17*2.3%,$C$12-sum($G$16:G16)),2))
Based on your table, it appears that the bonus is 2.3% of the positive
difference between sales and 2-month avg sales. The range $G$16:G16
computes the sum of all previous bonuses. Note that ":G16" is a
relative reference; it will change to ":G17" etc as you copy the
formula down the column. MIN selects the smaller of 2.3% of the
positive difference or an amount such that total bonuses does not
exceed C12. MAX(0,...) ensures that any negative result turns into
zero.
The formula in G16 might be:
=max(0,min(round(F17*2.3%,2),$C$12))
Alternatively, the formula in G16 could be the same as G17 etc, if G15
is always text or empty, and you change the SUM range to $G$15:G15,
which you can copy down the formula.
HTH.
----- original posting -----
my problem is that the plan bonus is hit for line 4, and should never come up
again because it is not possible to get any bonus once it it attained.
Simon Lloyd said:
Your formula works for the criteria you have given if H16 is greater
than 0 (True) then show 0, IF it is 0 or blank (False) then look at the
sum of F16:F17 if it is greater than or equal to C12 then SUM G16:G27
(True) if it is not greater than or equal to C12 then show 0 (False)
It's correct for each aspect, i dont understand your problem.
hcronrath;244830 Wrote:
The plan bonus should end once it equals the total growth bonus. Here is
the
formula that I use to calculate the Plan bonus:
where H16=Plan bonus, sum(f16:f17)=increase over best, C12= monthly
plan,
sum(g16:g27) =growth bonus
My data is below:
Monthly Plan $33,829
Monthly 2mo avg increase growth Plan
Sales over best bonus bonus
$7,089 $7,089 $7,089 $- $-
$16,385 $11,737 $4,648 $106.90 $-
$42,591 $29,488 $17,751 $408.27 $-
$25,636 $33,829 $4,341 $99.85 $615.03
$56,416 $33,829 $- $- $-
$5,630 $31,023 $- $- $615.03
