Thanks:  0
Likes:  0

# Thread: Find MIN for multiple ranges, with offset of 365 days

1. ## 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.26 1980 4 25.36

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

4. ## 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. ## Re: Find MIN for multiple ranges, with offset of 365 days

Originally Posted by QU4487
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!