Logical Function: IF + AND

[Scipio]

The formula in G37 reads: =IF(((G35+G36)<G31,G35+G36,G31)

However, that would return a negative number (1,557,306) in H37. So I need an IF/AND logical combination that says if (H35+H36)<H31 and H31 is >0, return H35+H36, otherwise return 0 (since H31 is negative, I can't show a negative Paid amount. Is there a logical combination of IF + AND that will make all of these scenarios work together in one formula that can be copied across row 37? The only other thing I can do is use the original formula for column G in column H and hard code a zero in H37, which is less than ideal. Would appreciate any help anyone out there could give me. Thanks in advance, Scipio.

 

[Joe4]

How about:

=MAX(MIN(G35+G36,G31),0)

 

[Scipio]

How about:

=MAX(MIN(G35+G36,G31),0)

I wasn't able to get this to work.

 

[Joe4]

I wasn't able to get this to work.

For column H, just change the "G"s to "H"s, i.e.

=MAX(MIN(H35+H36,H31),0)

That formula returns 396149 in cell G37 and 0 in cell H37, just like you stated you wanted.
Please give me a specific example that doesn't return what you think it should.

 

[Scipio]

For column H, just change the "G"s to "H"s, i.e.

=MAX(MIN(H35+H36,H31),0)

That formula returns 396149 in cell G37 and 0 in cell H37, just like you stated you wanted.
Please give me a specific example that doesn't return what you think it should.

After investigating the MAX/MIN functions used together, I understand it and have used the formula provided in your post. Many thanks Joe --- I appreciate the help!