+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    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
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,293

    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
    Attached Files Attached Files
    Last edited by etaf; 04-30-2013 at 09:22 AM.

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    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)
    Last edited by SunOffice; 04-30-2013 at 10:26 AM. Reason: Explained more...

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,293

    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
    Attached Files Attached Files
    Last edited by etaf; 04-30-2013 at 10:43 AM.

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1