Finding the MAX and MIN values in range within a range

[vwong107]

I've got some data collected from a test, and I'm trying to find the MAX and MIN values in a range within a range.

Example, from the test, I've collected a total of 100 data points (column A = record #1 thru 100). However, I'm only interested in the MAX and MIN values in column B (temp readings, for example) between record #10 thru 50. What should be the formulas for this?

Thanks very much.

[NBVC]

Try

=MAX(IF($A$1:$A$100>=10,$A$1:$A$100<=50,$B$1:$B$100))

edit ranges as necessary and confirm the formula with CTRL+SHIFT+ENTER not just ENTER...

likewise for MIN...

[vwong107]

Just tried that, but it returned a max value outside of the 10-50 range. Also, what does the CTRL+SHIFT+ENTER do that is different from ENTER?

[NBVC]

After entering the formula, hold the CTRL and SHIFT keys down, then press ENTER and you will see { } brackets appear around the formula.

This is called an Array Formula.

After doing that, do you still get incorrect results?

[vwong107]

After entering the formula, hold the CTRL and SHIFT keys down, then press ENTER and you will see { } brackets appear around the formula.

This is called an Array Formula.

After doing that, do you still get incorrect results?

Yes, it was still returning a max value outside of that range.

[NBVC]

Are you sure the values in column A are really numbers? Try selecting the column or range and go to Data|Text to columns and just click Finish.

Does that fix it? If not, please post sample workbook.. as I think it should work if column A is numeric.

[vwong107]

My data actually consists of over 6000 data points, so it might be too big to include. Anyway, I just created the following sample and it was still returning a MAX value outside of the range.

A B
1 98
2 105
3 100
4 100
5 101
6 99
7 103
8 100
9 97
10 100

I'm only interested in finding the MAX value in column B between the range of 3 and 6 in column A. In this example, if the formula is working, it should return a value of 101. However, it was returning a value of 105 instead.

[NBVC]

I do apologize, I forgot another IF in there... it should be:

=MAX(IF($A$1:$A$100>=3,IF($A$1:$A$100<=6,$B$1:$B$100)))

confirmed with CTRL+SHIFT+ENTER...

sorry 'bout that

[vwong107]

Thanks very much. It works now.