Nested IF Statement with limit on output value

[jb00976]

Hi, I have the following formula:
=IF(B3<=100000,0,IF(AND(B3>100000,B3<=250000),E3*0.06%, IF(AND(B3>250000,B3<=1000000), E3*0.1%, IF(AND(B3>1000000,B3<=5000000), E3*0.15%, IF(B3>5000000, E3*0.19%)))))

I want to limit the value it returns to be no greater than 15000, how do I do that.

Thanks

 

[Eric W]

Here's a way to do it without so many nested IFs:

=MIN(15000,LOOKUP(B3,{0,100001,250001,1000001,5000001},{0,0.06,0.1,0.15,0.19})/100*E3)

If you want to stick with your formula, just put the

=MIN(15000, . . . )

around your existing formula.

 

[jtakw]

Hi,

Good suggestion from Eric.

You can also shorten/simplify your IF statement like this:

Book1
	B	C	D	E
1		0.01
2
3	255000			10
Sheet31

Cell Formulas
Range	Formula
C1	=MIN(15000,IF(B3<=100000,0,E3*IF(B3<=250000,0.06%,IF(B3<=1000000,0.1%,IF(B3<=5000000,0.15%,0.19%)))))