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.