Results 1 to 5 of 5

Find MIN for multiple ranges, with offset of 365 days

This is a discussion on Find MIN for multiple ranges, with offset of 365 days within the Excel Questions forums, part of the Question Forums category; Hi, I am struggling quite a bit to find a solution; I am sure it's out there, but I just ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Find MIN for multiple ranges, with offset of 365 days

    Hi,

    I am struggling quite a bit to find a solution; I am sure it's out there, but I just don't find it (or get it). I am even not sure what really to search for...

    I have something like this:

    Year Day Value
    1979 1 26.405
    1979 2 26.496
    1979 3 26.582
    1979 4 26.672
    1979 5 26.770
    1979 6 26.867

    which continues till Day is 365, and then it start with 1980 and Day 1 again:

    1979 365 24.934
    1980 1 25.039
    1980 2 25.147
    1980 3 25.260
    1980 4 25.360

    This continues till recently (2015).

    Now, I would like to calculate the MIN value for each year over these 365 days, and put these in the following column, in order to have something like this:

    1979 16.855
    1980 16.139

    I thought something like MIN(OFFSET(xxxx)) could do it. But I just couldn't figure out how...

    Thanks for any hints how to achieve this.

  2. #2
    Board Regular
    Join Date
    Jul 2013
    Location
    West Country, UK
    Posts
    178

    Default Re: Find MIN for multiple ranges, with offset of 365 days

    Try this;

    Excel 2012
    A B C D E F
    1 Year Day Value Year Min Value
    2 1979 1 26.405 1979 24.934
    3 1979 2 26.496 1980 25.039
    4 1979 3 26.582
    5 1979 4 26.672
    6 1979 5 26.77
    7 1980 6 26.867
    8 1979 365 24.934
    9 1980 1 25.039
    10 1980 2 25.147
    11 1980 3 25.26
    12 1980 4 25.36
    Sheet1

    Array Formulas
    Cell Formula
    F2 {=MIN(IF(A:A=E2,C:C))}
    F3 {=MIN(IF(A:A=E3,C:C))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Hope it helps,

    Q

  3. #3
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,233

    Default Re: Find MIN for multiple ranges, with offset of 365 days

    @QU4487

    I strongly recommend against the use of entire column references within an array formula. Each of the formulas you give is being forced to calculate over more than a million cells.

    Many people don't seem to realise that, with array formulas, you cannot simply reference as many cells as you like without any detriment to performance. Unlike e.g. COUNTIF(S)/SUMIF(S), array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Board Regular
    Join Date
    Jul 2013
    Location
    West Country, UK
    Posts
    178

    Default Re: Find MIN for multiple ranges, with offset of 365 days

    Well pointed out.

    I was being lazy, as I was unsure of the precise row numbers.

    Cheers

  5. #5
    New Member
    Join Date
    Feb 2010
    Posts
    5

    Default Re: Find MIN for multiple ranges, with offset of 365 days

    Quote Originally Posted by QU4487 View Post
    Try this;

    Excel 2012
    A B C D E F
    1 Year Day Value Year Min Value
    2 1979 1 26.405 1979 24.934
    3 1979 2 26.496 1980 25.039
    4 1979 3 26.582
    5 1979 4 26.672
    6 1979 5 26.77
    7 1980 6 26.867
    8 1979 365 24.934
    9 1980 1 25.039
    10 1980 2 25.147
    11 1980 3 25.26
    12 1980 4 25.36
    Sheet1

    Array Formulas
    Cell Formula
    F2 {=MIN(IF(A:A=E2,C:C))}
    F3 {=MIN(IF(A:A=E3,C:C))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Hope it helps,

    Q

    Works perfectly! Thanks a lot!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com