MAX(IF and MIN(IF not giving results expected

alec1986

New Member
Joined
Sep 29, 2014
Messages
9
Hi all,

I have created a MAX(IF command in order to find the maximum temperature, from a table, between two times. it looks like this:

<h5,c16:c28226,0)))}, and="" it="" works="" great!
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
=MAX(IF(J16:J28226>G5,IF(J16:J28226"lessThan"H5,C16:C28226,0)))<h5,c16:c28226,0)))


Temperatures are tabled in C16:C28226
Times the temperatures were taken in J16:JJ28226
Reference Temperatures in G5 and H5.

I would also like to create exactly the same formula, using MIN, to look like this:
<h5,c16:c28226,0)))}
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))

=MAX(IF(J16:J28226>G5,IF(J16:J28226<h5,c16:c28226,0)))
"lessThan"H5,C16:C28226,0)))

But every time I do, it produces 0, insisting that is it 'FALSE'. Both formulas have {}, i just haven't added them here.

Can anyone see any logical reason why this might be?
Other times i get a 'false' reading are when the times (based in hours) are the same, next to each other (say, 9 as a minimum and 10 as a maximum) or inverse (say 10 as a minimum and 9 as a maximum).

Any help would be hugely appreciated, i feel like its something minor. Also, my apologies, the formulas will not paste correctly (even in plain text!) the both end with "Less than"H5,C16:C28226,0))). apologies for the poor formatting.
<h5,c16:c28226,0)))". sorry.

Thankyou!</h5,c16:c28226,0)))".></h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))}
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))},>
 
Last edited:

alec1986

New Member
Joined
Sep 29, 2014
Messages
9
Hi all,

I have created a MAX(IF command in order to find the maximum temperature, from a table, between two times. it looks like this:

Code:
<h5,c16:c28226,0)))}, and="" it="" works="" great!
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
=MAX(IF(J16:J28226>G5,IF(J16:J28226<H5,C16:C28226,0)))
<h5,c16:c28226,0)))


Temperatures are tabled in C16:C28226
Times the temperatures were taken in J16:JJ28226
Reference Temperatures in G5 and H5.

I would also like to create exactly the same formula, using MIN, to look like this:
<h5,c16:c28226,0)))}
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))
<h5,c16:c28226,0)))

Code:
=MIN(IF(J16:J28226>G5,IF(J16:J28226<h5,c16:c28226,0)))
<H5,C16:C28226,0)))
But every time I do, it produces 0, insisting that is it 'FALSE'. Both formulas have {}, i just haven't added them here.

Can anyone see any logical reason why this might be?
Other times i get a 'false' reading are when the times (based in hours) are the same, next to each other (say, 9 as a minimum and 10 as a maximum) or inverse (say 10 as a minimum and 9 as a maximum).

Any help would be hugely appreciated, i feel like its something minor. Also, my apologies, the formulas will not paste correctly (even in plain text!) the both end with "Less than"H5,C16:C28226,0))). apologies for the poor formatting.
<h5,c16:c28226,0)))". sorry.

Thankyou!</h5,c16:c28226,0)))".></h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))}
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))
</h5,c16:c28226,0)))},>
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
608
Maybe this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Temps</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Hours</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Time1</td><td style=";">Time2</td><td style="text-align: right;;"></td><td style="text-align: right;;">3:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;"></td><td style="text-align: right;;">4:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Min</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Max</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td><td style="text-align: right;;">8:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">12:00</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H8</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">(<font color="Green">$J$2:$J$28226>G5</font>)*(<font color="Green">$J$2:$J$28226<H5</font>),$C$2:$C$28226</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H9</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">(<font color="Green">$J$2:$J$28226>G5</font>)*(<font color="Green">$J$2:$J$28226<H5</font>),$C$2:$C$28226</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

alec1986

New Member
Joined
Sep 29, 2014
Messages
9
Worked perfectly, thank you! can I infer from this that instead of using IF after IF after IF, i can use *? does it do the same thing?
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
608
you're welcome

AND boolean logic uses * between 2 arrays for True & True in ($J$2:$J$28226>G5)*($J$2:$J$28226<h5< font="">)
</h5<>
<h5< font="">
IF(then IF(cut the array in half.


</h5<>
 

Forum statistics

Threads
1,082,607
Messages
5,366,593
Members
400,905
Latest member
xcelstudent

Some videos you may like

This Week's Hot Topics

Top