If function

[Mengo85]

Hi guys,

I would like to know how I can use an If function to determine the comission of 0.5% between £15,000 and £35,000. This is not a problem for an amout between or below the boundaries but what if the money put in is bigger?

For example, the amount put in is £60,000 there for the comission only applies between £15,000 and £35,000 which then is £100. but the fee of the other £25,000 between £35,000 and £60,000 does not apply.

Thank you in adavnce

Mengo85

[DonkeyOte]

If you assume sale amount is in A1 and commission in B1:

=MAX(0,MIN(35000,N(A1))-15000)*0.5%

or

=(MEDIAN(N(A1),{15000,35000})-15000)*0.5%

[JBeaucaire]

1) Sales below £15,000 get no commission
2) Sales above are .5% up to a max commission of £175

3) If the sale is entered into A1, then the B1 formula would be:

=IF(A1>=15000, MIN(A1 * 0.5%, 175), 0)

[Mengo85]

Excellent guys,

thank you very much! SOLVED