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

Copper Contributor

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
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'?
I am getting error when I remove K13+K24>=0
You have to remove the IF function completely from consideration:
MIN((K13+K24)/$C$14*$C$15,J14*$C$16))
Now the out is always 0.

The condition is that if IF(K13+K24>=0, then there is money to expand.

However, if there is money to expand, I want to expand at the pace the money allows for, yet never faster than the previous months tally **$C$16 (which is set at 1.2 or whatever we change it to).

@Helprequested123 

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

I have a scenario where if there is money, I have to expand the business by one of two numbers:

The previous months output* a constant *$C$16, or K13+K24)/$C$14*$C$15 -- whichever is less.
and if K13+K24 = $0, then no expansion at all.
Thank you for the formula, it is getting closer. If I change the MIN to a MAX, I am getting closer than if I leave it as MIN, but when the larger (MAX) answer comes back how can I cap that so it is the previous columns output * a constant ($C$16).

If I leave the MIN, I get one case where the output is a zero and all answers thereafter are '0', as that is the minimum.

If I change the MIN to MAX, I get growth, but it is run away growth that I need to cap.

@Helprequested123 

Difficult to diagnose without the real file or a number example to play with. 

 

 

Does excel have a function to cap the output to the previous columns output * a number?
..................................................................................................................................................................................
I am starting with: =MIN((E13+E24)/$C$14*$C$15,$C$15), and the output is 5.13 (all good).

I switch to: =IF((E13+E24)>=0,MAX((E13+E24)/$C$14*$C$15,E14*$C$16),0) in the next column and the output is 6.15, then 7.38, 8.86,10.63,29.34, 61.2 108 end larger and larger.

Once the out reaches 10.63 it starts to go geometric, so is there anyway to cap the output and the previous column's output * a constant (*$C$16)... what that happens to scale at because it is fixed growth vs runaway growth.

It's almost like I need an IF with a MAX and a MIN or at least a cap on the MAX.

@Helprequested123 Attach a screenshot or send me the file via a direct message!

I am thinking there must be an AND, OR I need so that when the J14 jumps to 29.34, I can limit that to I14*$C$15.

$C$15 is set at 1.2 and not shown

@Helprequested123 Sorry, this is useless. You are not showing the formulae in place, nor does it show the constants that you reference in column C.

Thank you. I understand. I'm getting closer, just not there yet.
What happens if the K13+K24>=0 is not met? Would it show J14*$C$16?

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.