Sorry joeu2004, I was wrong. Reason is that I am French and we use the comma
as decimal separator and I thought that on the formula max(0,10%... you
actually meant 0.10% (what Schorn wanted), and therefore your max function
had only one argument for me, returning a negative value.

"joeu2004" wrote:

On Jul 21, 12:32 pm, Stephane Quenson
wrote:
"joeu2004" wrote:
=45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) +
max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000))

Sorry to inform you that your formula returns a wrong result.
Test it with A1 = 1 for example, you get a negative amount

I get 0.45, as expected. The logic of the first term is: use the
smaller of A1 or 100000. Since 1 is smaller, min(...) should return
1, which is then multiplied by 45%. The other min(...) terms will
return negative numbers, but they are nullified because max(0,...)
will return 0 in those cases.

I do notice one potentially undesirable behavior. The formula works
fine if A1 is an explicit 0. But if A1 is blank, min(A1,100000)
returns 100000(!). This can be fixed by replacing A1 with n(A1) in
the first term. That is:

=45%*min(n(A1),100000) + ....

N() is not required in the other terms because Excel treats a blank
cell as zero in an arithmetic expression (e.g. A1-100000). On the
other hand, if the cell __appears__ blank, but it actually contains a
formula that returns the null string ("") sometimes, it might be
prudent to replace A1 with N(A1) in all instances. Isn't Excel
grand? (Rhetorical.)

PS: I did not bother to bullet-proof the formula for A1<0. KISS.
The OP says that A1 (in my example) represents the "total of
investments under management". Presumably, that is non-negative. But
if negative A1 is a possibility, then yes, replacing A1 in the first
term with max(0,A1) fixes both problems. That is:

=45%*min(max(0,A1),100000) + ....

Again, max(0,A1) is required only in the first term because the
max(0,min(...)) takes care of the problem in the other terms.

Your
table approach is very good, again check your formulas in column C as they
return negative values for amounts lower than the threshold. Use MAX(0, your
formula) to solve it.

Again, I did not feel the need to bullet-proof against having a
negative "total of investments under management". But if that is
desirable, I would simply do:

=if(A1<0, 0, (A1-vlookup(A1,table,1))*....)