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.

Hi Blue Hornet,

How do I explain this? (I didn't design this incentive, just doing a
reproting spreadsheet for the reps, if it were up to me, I'd just do a cell
for the total.) The reps need to see where they stand on each of the 3
components. There is already a cell for the total of the 3. Since 'x'*5 can
calculate to over $400, I don't want any of the 3 cells (A1, B1 or C1) to
show over 400 as they don't get paid over 400.

I want each of the 3 cells to stop at 400 (which I can do), but when one
reaches 400 (the cap), I don't want the other 2 to have value that one could
construe as being added to the total since this will be an official written
document of the company.

I know it's complex, I appreciate your help.

Thanks, CP

"Blue Hornet" wrote:

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.

You're restating without adding significant information.

What does "I don't want the other 2 to have value that one could
construe as being added to the total" mean? How do we determine what to
construe?

Do you really mean that you could have something like:

A B C D
1 79 79 79
2 395 395 395 1185


where A2:C2 are 5 * A1:C1 and D2 is the sum of A2:C2.

Then if 80 is entered in B2 you get

A B C D
1 79 80 79
2 0 400 0 400

???

That's easy, though to me, nonsensical:

A2: =IF(OR(B1=80,C1=80),0,MIN(80,A1)*5)
B2: =IF(OR(A1=80,C1=80),0,MIN(80,B1)*5)
C2: =IF(OR(A1=80,B1=80),0,MIN(80,C1)*5)

In article ,
"CP" wrote:

How do I explain this? (I didn't design this incentive, just doing a
reproting spreadsheet for the reps, if it were up to me, I'd just do a cell
for the total.) The reps need to see where they stand on each of the 3
components. There is already a cell for the total of the 3. Since 'x'*5 can
calculate to over $400, I don't want any of the 3 cells (A1, B1 or C1) to
show over 400 as they don't get paid over 400.

I want each of the 3 cells to stop at 400 (which I can do), but when one
reaches 400 (the cap), I don't want the other 2 to have value that one could
construe as being added to the total since this will be an official written
document of the company.

Hi JE,

Thanks, you got it!! It works great if one cell is =80, but if 2 cells
are =80, then A1, B1, C1 all show 0.

A2: =IF(OR(B1=80,C1=80),0,MIN(80,A1)*5)
B2: =IF(OR(A1=80,C1=80),0,MIN(80,B1)*5)
C2: =IF(OR(A1=80,B1=80),0,MIN(80,C1)*5)

Works fine for this scenario,

A B C D
1 79 80 79
2 0 400 0 400

But here's what I get for this one:

A B C D
1 79 80 81
2 0 0 0 0

Thanks, we are making progress!

CP


"JE McGimpsey" wrote:

You're restating without adding significant information.

What does "I don't want the other 2 to have value that one could
construe as being added to the total" mean? How do we determine what to
construe?

Do you really mean that you could have something like:

A B C D
1 79 79 79
2 395 395 395 1185


where A2:C2 are 5 * A1:C1 and D2 is the sum of A2:C2.

Then if 80 is entered in B2 you get

A B C D
1 79 80 79
2 0 400 0 400

???

That's easy, though to me, nonsensical:

A2: =IF(OR(B1=80,C1=80),0,MIN(80,A1)*5)
B2: =IF(OR(A1=80,C1=80),0,MIN(80,B1)*5)
C2: =IF(OR(A1=80,B1=80),0,MIN(80,C1)*5)

In article ,
"CP" wrote:

How do I explain this? (I didn't design this incentive, just doing a
reproting spreadsheet for the reps, if it were up to me, I'd just do a cell
for the total.) The reps need to see where they stand on each of the 3
components. There is already a cell for the total of the 3. Since 'x'*5 can
calculate to over $400, I don't want any of the 3 cells (A1, B1 or C1) to
show over 400 as they don't get paid over 400.

I want each of the 3 cells to stop at 400 (which I can do), but when one
reaches 400 (the cap), I don't want the other 2 to have value that one could
construe as being added to the total since this will be an official written
document of the company.