MAX(IF and MIN(IF not giving results expected

[alec1986]

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)))},>

 

[alec1986]

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<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)))

=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]

Maybe this?

Book1
	C	D	E	F	G	H	I	J
1	Temps							Hours
2	24							1:00
3	30							2:00
4	32				Time1	Time2		3:00
5	56				4:00	9:00		4:00
6	46							5:00
7	12							6:00
8	30				Min	12		7:00
9	32				Max	46		8:00
10	56							9:00
11	40							10:00
12	26							11:00
13	30							12:00
Sheet1

Cell Formulas
Range	Formula
H8	{=MIN(IF(($J$2:$J$28226>G5)*($J$2:$J$28226),$C$2:$C$28226))}
H9	{=MAX(IF(($J$2:$J$28226>G5)*($J$2:$J$28226),$C$2:$C$28226))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alec1986]

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]

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<>