Hi,

I want to calculate results of an incentive that has 3 components all of
which are 'x' times $5 to calculate their total bonus.

Any combo of the 3 can reach a cap of $400. My question is when one of the
3 cells reaches the cap of $400, how do I get the other 2 to default to 0
without there being a circular reference?

Thanks

Your question appears to me to be ambiguous as stated.

If any combo can reach a cap of 400, what happens in the case that, say:

A1: 100
B1: 200
C1: 100

and A1 increases to 150. How should B1 and C1 be adjusted?



In article ,
"CP" wrote:

Hi,

I want to calculate results of an incentive that has 3 components all of
which are 'x' times $5 to calculate their total bonus.

Any combo of the 3 can reach a cap of $400. My question is when one of the
3 cells reaches the cap of $400, how do I get the other 2 to default to 0
without there being a circular reference?

Thanks

Hi JE,

That's all fine...it can add up to 400, it's when any one of A1 or B1 or C1
get to 400, then I want the other two cells to go to 0.

In other words, since there is a cap of 400, any one of the 3 cells can't
show over 400. I know how yo do a MAX function, but that isn't helping me
with this. I need 2 cells to be 0 if one has reached 400.

Thanks, CP

"JE McGimpsey" wrote:

Your question appears to me to be ambiguous as stated.

If any combo can reach a cap of 400, what happens in the case that, say:

A1: 100
B1: 200
C1: 100

and A1 increases to 150. How should B1 and C1 be adjusted?



In article ,
"CP" wrote:

Hi,

I want to calculate results of an incentive that has 3 components all of
which are 'x' times $5 to calculate their total bonus.

Any combo of the 3 can reach a cap of $400. My question is when one of the
3 cells reaches the cap of $400, how do I get the other 2 to default to 0
without there being a circular reference?

Thanks

CP, I don't think you really answered JEM's question, because it's not
clear how to "change" the B or C numbers (either? both? when A + B + C
400.

Maybe you don't really care?

Perhaps what you're looking for is:
=MIN( A1 + B1 + C1, 400)

not MAX. In other words, if A1 + B1 + C1 < 400, then that is the
amount to use. If A + B + C 400, then you want the 400, and you may
not care about the components as individual values.

Hi Blue Hornet,

Thanks for your response. A1,B1 & C1 all derive their individual value as
'x' times $5. Would the formula you suggested require a 4th cell? If not,
then it would be an If statement such as ="x"*5,your formula. X referring to
another cell wher you put in the amount you do, the that is multiplied by $5
to get the A1, B1 and/or C1 that each can't go over $400. This is where I
need the help.

Thanks, CP

"Blue Hornet" wrote:

CP, I don't think you really answered JEM's question, because it's not
clear how to "change" the B or C numbers (either? both? when A + B + C
400.

Maybe you don't really care?

Perhaps what you're looking for is:
=MIN( A1 + B1 + C1, 400)

not MAX. In other words, if A1 + B1 + C1 < 400, then that is the
amount to use. If A + B + C 400, then you want the 400, and you may
not care about the components as individual values.

It's still not clear enough exactly what you're trying to achieve. If
you're paying an incentive based on three components, but you don't
want the incentive payment to be $400, then you can do it the way I
suggest:
= MIN(( A + B + C) * 5, 400)
and you don't have to change any of the individual components. What
would be the point? It seems to me that the point is to arrive at "the
incentive payment", not "the components of the incentive".

If you're trying to get somewhere else, it's not clear from your
posting where that would be. If your components a
Appearance, Promptness and Accuracy, for example, you always want to
maximize those, right? You don't want to rank one of them back to 0
just because a max incentive "payment" has been achieved. Sort of
defeats the purpose of the incentive, no?

Anyway, it's all just idle speculation on my part until I really
understand where you're going.