Forum Discussion

Helprequested123's avatar
Helprequested123
Copper Contributor
Jan 11, 2022

Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nested

I am using this formula:

 

=MIN(IF(K13+K24>=0,(K13+K24)/$C$14*$C$15,J14*$C$16))

 

The end is the lower of the two results (J14*$C$16). In this case J14*$C$16 = 46.8

However, K13+K24)/$C$14*$C$15 = 65.8   and that is what excel is outputting.

I understand that K13+K24)/$C$14*$C$15 = 65.8 is greater than or equal to zero, so that condition is met, but how can I get the cell to choose the MINIMUM number which is the latter J14*$C$16 = 46.8???

18 Replies

  • NowshadAhmed's avatar
    NowshadAhmed
    Iron Contributor

    This formula should give what you need (based on what I understood):

    =IF(MIN(IF(F13+F24>=0,(F13+F24)/$C$14*$C$15,E14*$C$16),E14*$C$16)<>0,MIN(IF(F13+F24>=0,(F13+F24)/$C$14*$C$15,E14*$C$16),E14*$C$16),E14*$C$16)

    Also, this formula does not take Column D into consideration, so it gives 0 result in column E which then travels forward. If you can resolve what the formula should do in column D or what the results would be in column E, this should help you.

  • NowshadAhmed's avatar
    NowshadAhmed
    Iron Contributor
    The problem in your formula is that the MIN function is only seeing the end result of the IF function. You need to show both the results to the MIN function for comparison at the same time.
    I could hand you the formula but try to see if you can find a way.
    Hint: Since you need the minimum of two, do you really need the condition 'K13+K24>=0'?
      • NowshadAhmed's avatar
        NowshadAhmed
        Iron Contributor
        You have to remove the IF function completely from consideration:
        MIN((K13+K24)/$C$14*$C$15,J14*$C$16))

Resources