Set a Maximum value for "if" formula

[sabuviva]

Hi all,
I am new to this and i have a question.

does anyone know how i can set a maximum value for an "if" formula?
I am calculating an accumulative rebate system. and my formula is as such,
=IF(BY10>=10000,(BY10-10000)*$CB$6,0) --> read as, if BY10 greater than 10,000(in value), then we take BY10 - 10k * % of rebate.
but this must be capped at 40k. because this rebate % is only for between 10k - 40k.
how can i make a cap on this formula?

thanks a lot for your help!

S.

[Pete_UK]

Try this:

=IF(BY10>=10000,MIN(40000,(BY10-10000)*$CB$6),0)

Hope this helps.

Pete

[wenqq3]

=IF(BY10>=10000,Min(((BY10-10000)*$CB$6),40k),0)

if min is 10k, then

=IF(BY10>=10000,Max(10k,Min(((BY10-10000)*$CB$6),40k)),0)

[Roger Govier]

Hi

try
=IF(BY10>=10000,(MIN(BY10,40000)-10000)*$CB$6,0)