Help with IF / AND

[shahdelsol]

I am using this IF/AND formula and it works but in certain scenario doesn't work the way I want it.

=IF(AND(M41>=12,M41<=14.5,AD41>=40),M41*50,IF(AND(M41>=15,M41<=17.5,AD41>=55),(M41*50)+700,IF(AND(M41>=18,M41<=19.5,AD41>=65),(M41*50)+700+850,IF(AND(M41>=20,AD41>=75),(M41*100)+700+850+950,0))))

As you see there are 4 parts with IF/AND.
The problem is if AD41 on the second IF <55 but >39, and still M41>=15,M41<=17.5 true, I still want 14*50

If AD41 on the third IF <65 but >54 and still M41>=18,M41<=19.5 true, I still want (17*50)+700

If AD41 on the forth IF <75 but >64 and still M41>=20, I still want (19*50)+700+850

I hope this all makes sense. Is there a better way to handle this without adding more IF / ANDs? Thanks in advance. ​

​

 

[shahdelsol]

This is getting really close to what I want. There is only one small issue. If M41 is 14.5 then cap is 2.5 but if M41 is 15 or bigger then cap is 2, Is there a solution for that? If not I just take this for now.

This sets the cap at 2.5
=IF(AND(M41>=12,AD41>=40),$600 + (MIN(M41-12, 2.5)*50),0

 

[AlphaFrog]

if m41 is 14.5 then cap is 2.5 but if m41 is 15 or bigger then cap is 2, is there a solution for that? If not i just take this for now.

Try...

=IF(and(m41>=12,ad41>=40),$600 + (min(m41-12, 2+((m41=14.5)*0.5)*50),0

 

[shahdelsol]

It doesn't return the correct result. If M41 is 12.5 the result should be 625 but this shows 600.50, if the M41 is 13 the result should be 650 but this shows 601. Any idea?

Try...

=IF(and(m41>=12,ad41>=40),$600 + (min(m41-12, 2+((m41=14.5)*0.5)*50),0

 

[shahdelsol]

Thank you for all the help. I figured this out and this does what exactly I am looking for:

=IF(and(m41>=12,ad41>=40), 600 + (MIN(M41-12, 2+(M41=14.5))*50),0)​

Try...

=IF(and(m41>=12,ad41>=40),$600 + (min(m41-12, 2+((m41=14.5)*0.5)*50),0