Max and Min in a Range

[BRob]

I've got a 300 row spreadsheet and I want to calculate the Max and Min
values in Column A based on an integer in Column B.


In the example below I'd like to be able to report Max(11) is 230; Min(11)
is 211; Max(10) is 200; Max(10) is 195.

Columns A and B will always be integers in descending order.

Can SKS help out

Tx

Rob


230 11
226 11
220 11
220 11
211 11
200 10
198 10
196 10
195 10

 

[Pete_UK]

You could do it with a couple of array* formulae. Put the labels Max
and Min in D1 and E1, and put 10 and 11 in C2 and C3, then put this
array* formula in D2:

=MAX(IF(B$1:B$300=C2,A$1:A$300))

and put this array* formula in E2:

=MIN(IF(B$1:B$300=C2,A$1:A$300,10E10))

* As these are array formulae then you must use CTRL-SHIFT-ENTER (CSE)
to commit them rather than the usual ENTER. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - you must not type these yourself. If you edit/
amend the formula, you must use CSE again to commit it.

Copy the two formulae into D3:E3, and then you will have your results
in a little table.

Hope this helps.

Pete