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

#### luftikus143

##### New Member
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

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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

<colgroup><col><col></colgroup><tbody>
</tbody>

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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### QU4487

##### Board Regular
Try this;

Excel 2012
ABCDEF
1YearDayValueYearMin Value
21979126.405197924.934
31979226.496198025.039
41979326.582
51979426.672
61979526.77
71980626.867
8197936524.934
91980125.039
101980225.147
111980325.26
121980425.36

</tbody>
Sheet1

Array Formulas
CellFormula
F2{=MIN(IF(A:A=E2,C:C))}
F3{=MIN(IF(A:A=E3,C:C))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope it helps,

Q

#### XOR LX

##### Well-known Member
@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

#### QU4487

##### Board Regular
Well pointed out.

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

Cheers

#### luftikus143

##### New Member
Try this;

Excel 2012
ABCDEF
1YearDayValueYearMin Value
21979126.405197924.934
31979226.496198025.039
41979326.582
51979426.672
61979526.77
71980626.867
8197936524.934
91980125.039
101980225.147
111980325.26
121980425.36

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
F2{=MIN(IF(A:A=E2,C:C))}
F3{=MIN(IF(A:A=E3,C:C))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope it helps,

Q

Works perfectly! Thanks a lot!

Replies
9
Views
171
Replies
25
Views
1K
Replies
9
Views
579
Replies
4
Views
298
Replies
5
Views
659

1,126,966
Messages
5,621,874
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back