Based on Years calcuate Min() and Max() values from another column

Hi,

I have two columns - a) Column A has Year values, b) Column B has Rate Values as per Years. Like below table data:
Year Rate
2007 10
2007 0
2008 45
2007 54
2007 26
2009 30
2008 4
2007 31
2007 12
2009 44
2007 75
2010 24
I need to calculate >-----> Min() and Max() values from Column B for each Year.

Can anyone please help me to calculate results like below:
Results:
Year Min Max
2007 0 75
2008 4 45
2009 30 44
2010 24 24

If Year is in E1
and the data is in A1 and B1

=IF($E1="","",MIN(IF($A$1:$A$12=$E1,$B$1:$B$12)))
=IF($E1="","",MAX(IF($A$1:$A$12=$E1,$B$1:$B$12)))

enter as a array

Control+shift+enter

See Sheet1

OR you could use a pivottable - see PVT sheet

Thank you etaf!

It's working fine. Can you/anyone help me on below three points:
1) As per your excel file year 2011 and 2012 is giving 0 (zero) as there is no data for 2011 and 2012. Can It be shown as "NA" or "Not Mentioned"?
2) Entering your formulas as array (Control+shift+enter) >-----> Completely new for me, Can you/anyone plz explain it more for me (as taking above example).
3) Can there be Subtotal formula use for the results? (If 'Yes', I don't know how to place this trick here)

FOR 1)
=IF(COUNTIF($A$2:$A$13,$E2)=0,"Not Mentioned",MIN(IF($A$2:$A$13=$E2,$B$2:$B$13)))
=IF(COUNTIF($A$2:$A$13,$E2)=0,"Not Mentioned",MAX(IF($A$2:$A$13=$E2,$B$2:$B$13)))

FOR 2)
http://office.microsoft.com/en-gb/ex...001087290.aspx

FOR 3)
Where do you need the subtotal - dont understand sorry -unless you mean add up the rates by year =SUMIF($A$2:$A$13,E2,$B$2:$B$13) - and you could use the countif() in an IF to put the not mentioned in - just a straight formula - no arrays needed

see attached modified sheet columns J,K,L

Hi SunOffice

You have Excel 2010, in this case you have no need for array formulas. Use the aggregate function. Assuming your data is A1:B13 including headers and the year criteria is in F2: F5
For MIN, in G2:
=AGGREGATE(15,6,$B$2:$B$13/($A$2:$A$13=F2),1) enter as usual & copy down.

For MAX in H2:
=AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=F2),1) enter as usual & copy down.