Find MIN for multiple ranges, with offset of 365 days

luftikus143

New Member
Joined
Feb 25, 2010
Messages
5
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:

YearDayValue
1979126.405
1979226.496
1979326.582
1979426.672
1979526.770
1979626.867

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

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

197936524.934
1980125.039
1980225.147
1980325.260
1980425.360

<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:

197916.855
198016.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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><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
 
Upvote 0
@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
 
Upvote 0
Well pointed out.

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

Cheers
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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?

Disable AdBlock

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
Back
Top