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

1. ## 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.

Results:
Year Min Max
2007 0 75
2008 4 45
2009 30 44
2010 24 24  Register To Reply

2. ## Re: Based on Years calcuate Min() and Max() values from another column

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  Register To Reply

3. ## Re: Based on Years calcuate Min() and Max() values from another column

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)  Register To Reply

4. ## Re: Based on Years calcuate Min() and Max() values from another column

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  Register To Reply

5. ## Re: Based on Years calcuate Min() and Max() values from another column

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.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 