Maximum result in calculated field

[jef]

Have the following formula in cell AU:

=ROUND(IF(BA6>9,J6*0.4,IF(BA6>7,J6*0.35,IF(BA6>5,J6*0.3,IF(BA6<6,1,0))))*1000,-5)/
1000

It does the following:

If a previously calculated cell (BA) is 0-5, then AU = 0;
If BA is 6-7, then AU = 30% x J;
If BA is 8-9, then AU = 35% x J;
If BA is 10 or more, then AU = 40% x J;
And the result is rounded. This is why the last part of the formula
is *1000,-5)/1000

What I need to add to this formula is a limit to the final calculated
result. Currently, there is no maximum result. I would like the
following maximum results:

If BA is 6-7, the maximum result should be 10,000;
If BA is 8-9, the maximum result should be 20,000;
If BA is 10 or more, the maximum result should be 30,000;

For example, if:

J = 100,000;
BA = 8;
Then AU currently shows the result as 35,000 with no limitations
I need the formula to produce a limiting result of 20,000.

If there needs to be another separate formula that acts on cell AU,
that’s OK as well.

Thanks for anyone’s help.

 

[joeu2004]

Ostensibly:

=ROUND(IF(BA6<6,0,IF(BA6<8,MIN(10000,J6*30%),
IF(BA6<10,MIN(20000,J6*35%),MIN(30000,J6*40%))))*1000,-5)/1000

I believe you can simpify that to:

=ROUND(IF(BA6<6,0,IF(BA6<8,MIN(10000,J6*30%),
IF(BA6<10,MIN(20000,J6*35%),MIN(30000,J6*40%)))),-2)


----- original message -----

 

[jef]

Ostensibly:

=ROUND(IF(BA6<6,0,IF(BA6<8,MIN(10000,J6*30%),
IF(BA6<10,MIN(20000,J6*35%),MIN(30000,J6*40%))))*1000,-5)/1000

I believe you can simpify that to:

=ROUND(IF(BA6<6,0,IF(BA6<8,MIN(10000,J6*30%),
IF(BA6<10,MIN(20000,J6*35%),MIN(30000,J6*40%)))),-2)

----- original message -----

Thanks a bunch, worked like a charm.