Discussion Re: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest in Excel
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056808#M129266
and if K13+K24 = $0, then no expansion at all.<BR />Tue, 11 Jan 2022 06:55:26 GMTHelprequested1232022-01-11T06:55:26ZMin Function Nested inside an IF and/or the other way, but can't get it to work on formulas nested
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056766#M129252
<P>I am using this formula:</P><P> </P><P>=MIN(IF(K13+K24>=0,(K13+K24)/$C$14*$C$15,J14*$C$16))</P><P> </P><P>The end is the lower of the two results (J14*$C$16). In this case J14*$C$16 = 46.8<BR /><BR />However, K13+K24)/$C$14*$C$15 = 65.8 and that is what excel is outputting.<BR /><BR />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???</P>Tue, 11 Jan 2022 06:03:48 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056766#M129252Helprequested1232022-01-11T06:03:48ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056775#M129253
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.<BR />I could hand you the formula but try to see if you can find a way.<BR />Hint: Since you need the minimum of two, do you really need the condition 'K13+K24>=0'?Tue, 11 Jan 2022 06:23:20 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056775#M129253NowshadAhmed2022-01-11T06:23:20ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056789#M129258
I am getting error when I remove K13+K24>=0Tue, 11 Jan 2022 06:38:18 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056789#M129258Helprequested1232022-01-11T06:38:18ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056798#M129262
You have to remove the IF function completely from consideration:<BR />MIN((K13+K24)/$C$14*$C$15,J14*$C$16))Tue, 11 Jan 2022 06:47:42 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056798#M129262NowshadAhmed2022-01-11T06:47:42ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056802#M129263
Now the out is always 0.<BR /><BR />The condition is that if IF(K13+K24>=0, then there is money to expand.<BR /><BR />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).<BR />Tue, 11 Jan 2022 06:51:28 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056802#M129263Helprequested1232022-01-11T06:51:28ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056804#M129264
<P><LI-USER uid="1130564"></LI-USER> </P><P>=IF((K13+K24)>=0,MIN((K13+K24)/$C$14*$C$15,J14*$C$16),0)</P>Tue, 11 Jan 2022 06:53:03 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056804#M129264Riny_van_Eekelen2022-01-11T06:53:03ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056805#M129265
I have a scenario where if there is money, I have to expand the business by one of two numbers:<BR /><BR />The previous months output* a constant *$C$16, or K13+K24)/$C$14*$C$15 -- whichever is less.Tue, 11 Jan 2022 06:53:12 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056805#M129265Helprequested1232022-01-11T06:53:12ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056808#M129266
and if K13+K24 = $0, then no expansion at all.<BR />Tue, 11 Jan 2022 06:55:26 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056808#M129266Helprequested1232022-01-11T06:55:26ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056813#M129267
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).<BR /><BR />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.<BR /><BR />If I change the MIN to MAX, I get growth, but it is run away growth that I need to cap.<BR />Tue, 11 Jan 2022 07:07:44 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056813#M129267Helprequested1232022-01-11T07:07:44ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056816#M129268
<P><LI-USER uid="1130564"></LI-USER> </P><P>Difficult to diagnose without the real file or a number example to play with. </P><P> </P><P> </P>Tue, 11 Jan 2022 07:15:59 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056816#M129268Riny_van_Eekelen2022-01-11T07:15:59ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056818#M129269
Does excel have a function to cap the output to the previous columns output * a number?<BR />..................................................................................................................................................................................<BR />I am starting with: =MIN((E13+E24)/$C$14*$C$15,$C$15), and the output is 5.13 (all good).<BR /><BR />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.<BR /><BR />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.<BR /><BR />It's almost like I need an IF with a MAX and a MIN or at least a cap on the MAX.Tue, 11 Jan 2022 07:17:35 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056818#M129269Helprequested1232022-01-11T07:17:35ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056821#M129270
<P><LI-USER uid="1130564"></LI-USER> Attach a screenshot or send me the file via a direct message!</P>Tue, 11 Jan 2022 07:23:35 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056821#M129270Riny_van_Eekelen2022-01-11T07:23:35ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056846#M129274
<P><LI-USER uid="403176"></LI-USER> </P>Tue, 11 Jan 2022 07:43:31 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056846#M129274Helprequested1232022-01-11T07:43:31ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056850#M129275
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.<BR /><BR />$C$15 is set at 1.2 and not shown<BR />Tue, 11 Jan 2022 07:47:43 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056850#M129275Helprequested1232022-01-11T07:47:43ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056852#M129276
<P><LI-USER uid="403176"></LI-USER> </P>Tue, 11 Jan 2022 07:48:17 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056852#M129276Helprequested1232022-01-11T07:48:17ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056873#M129280
<P><LI-USER uid="1130564"></LI-USER> Sorry, this is useless. You are not showing the formulae in place, nor does it show the constants that you reference in column C.</P>Tue, 11 Jan 2022 08:08:24 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056873#M129280Riny_van_Eekelen2022-01-11T08:08:24ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056874#M129281
Thank you. I understand. I'm getting closer, just not there yet.<BR />Tue, 11 Jan 2022 08:09:39 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056874#M129281Helprequested1232022-01-11T08:09:39ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056965#M129291
What happens if the K13+K24>=0 is not met? Would it show J14*$C$16?Tue, 11 Jan 2022 09:51:54 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056965#M129291NowshadAhmed2022-01-11T09:51:54ZRe: Min Function Nested inside an IF and/or the other way, but can't get it to work on formulas nest
https://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056969#M129292
<P>This formula should give what you need (based on what I understood):<BR /><BR />=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)<BR /><BR />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.</P>Tue, 11 Jan 2022 10:06:33 GMThttps://techcommunity.microsoft.com/t5/excel/min-function-nested-inside-an-if-and-or-the-other-way-but-can-t/m-p/3056969#M129292NowshadAhmed2022-01-11T10:06:33Z