Limit the result of a complex formula

[crodz2]

Hello All,

Good day!

I need help in managing a formula please.

This is my existing formula:


=if(A4="Refunded",-20%*(15%*L4),L4-M4-N4)


What I want to happen is make sure that if A4 is REFUNDED, that the result will not go over -5. I tried inserting the MAX function but I always end up with an error.

Any help is much appreciated.

Regards,
Rod

[FDibbins]

Hi, welcome to the forum

Try this...
=if(A4="Refunded",max(-5,-20%*(15%*L4)),L4-M4-N4)

[crodz2]

Hello Ford,

Good day!

The formula was able to capture the MAX limit, however, something came up.

Even if cell A4 does not contain the word "REFUNDED" it will capture the computation as if REFUNDED is there. Not sure if I am making sense but what I am trying to accomplish is create a formula wherein:

=if(A4="Refunded",-20%*(15%*L4),L4-M4-N4)

1. If the value of cell A# is NOT refunded, it will just compute as L#-M#-N4.
2. If the value of cell A# is REFUNDED, it will compute as -20%*(15%*L#).
3. The outcome if cell A# has REFUNDED should not exceed -5.

Thank you for response. I appreciate it a lot.

Sincerely,
Rod

[José Augusto]

Hi Rod

The Ford answer seems to be good
See the file

[crodz2]

Hello Jose & Ford,

Thank you so much for your help!

I think there was just a glitch when I ran the formula. It is working ok now.

Thank you! Thank you! Thank you!

Regards,
Rod

[FDibbins]

Happy to help