How can I return zero after a max value has been met?

[hcronrath]

The plan bonus should end once it equals the total growth bonus. Here is the
formula that I use to calculate the Plan bonus:

=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

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

 

[hcronrath]

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.

 

[joeu2004]

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.

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.

 

[joeu2004]

Errata for some obvious(?) typos ....

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):

That should read F17 instead of F16.

The formula in G16 might be:
=max(0,min(round(F17*2.3%,2),$C$12))

That should read F16 instead of F17.


----- original posting -----

 

[hcronrath]

Thanks for your very detailed explanation. I am sorry for the confusion.
What I am trying to accomplish is computing the Plan Bonus in cells H16:H27,
but that bonus can NEVER be more than the sum of the Growth Bonus & once the
Plan Bonus is reached it & all other bonuses will cease. From my example
table you can see that the Plan Bonus of $615.03 is repeated, I don't want it
to repeat once it has been achieved. I hope that this has clarified my
problem, I am new to this, so I apologize for my stumbling.
--
cheers,
Heather

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.

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

 

[joeu2004]

Thanks for your very detailed explanation.
I am sorry for the confusion.

And forgive me if I am being dense, which happens sometimes when I try
to read too much between the lines.

What I am trying to accomplish is computing the
Plan Bonus in cells H16:H27, but that bonus can
NEVER be more than the sum of the Growth Bonus
& once the Plan Bonus is reached it & all other
bonuses will cease.

How is the Plan Bonus computed or determined?

In your previous posting, it seems to be the sum of Growth Bonus.
Coincidence? In fact, Plan Bonus seems to be the sum of all 12(!)
monthly Growth Bonus values; but I suspect you mean the year-to-date
sum.

The point is: it is circular reasoning to say that the Plan Bonus is
the sum, but it should stop when that sum reaches the Plan Bonus.

Do you see the problem I am having in understanding your requirement?

once the Plan Bonus is reached it & all other
bonuses will cease.

Does "all other bonuses" include the Growth Bonus?

If that is the case, that would have to be reflected in the formula in
column G, which I assume is the column labeled Growth Bonus in your
example table.

I will concentrate on the formula in column H, which I assume is the
column labeled Plan Bonus. That is what your original posting asked
about.

We can return to the Growth Bonus formula later, if you wish.

From my example table you can see that the Plan
Bonus of $615.03 is repeated, I don't want it
to repeat once it has been achieved.

Okay. But your table would show the Plan Bonus only in row 19, not
above it. (I assume the 1st Monthly Sales figure, $7089, is row 16.)

Why is that? Is that another part of the requirement, which you have
not articulated? Or is it simply part of the problem with your
formulas, which you would like help in correcting?

In other words, instead of showing how the table looks (erroneously),
show how do you want the table to look.


Based on my observation (which might misleading; take the following
with a grain of salt), I wonder if you mean to say: the Plan Bonus is
recorded the first time the sum of Growth Bonus equals (or would
exceed?) the Plan Bonus.

If that is the case, the following formula might work for you. (It can
be written many ways; I hope the following is clearest.)

=if(and(countif($H$15:H15,">0")=0, sum($G$15:G16)>=$D$12), $D$12, 0)

I arbitrarily assume that the Plan Bonus (or max Plan Bonus?) is in
D12. Note that ranges like $H$15:H15 and $G$15:G16 automatically
change to $H$15:H16, $H$15:H17 etc and to $G$15:G17, $G$15:G18 etc as
you copy the formula down the colulmn.

The formula says: if the Plan Bonus has not been recorded in this
column above, and if the year-to-date sum of Growth Bonus equals or
exceeds the Plan Bonus, record the Plan Bonus here. Because of the
COUNTIF, this will happen only once.

Is that getting any closer to your requirement?


PS: There are other inconsistencies between your English language
description and the actual computations in the example table. We can
discuss later, too, if you wish. At the very least, it might help me
to better understand your requirements.



----- original posting -----