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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

QU4487

Board Regular
Joined
Jul 3, 2013
Messages
178
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
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
@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
Joined
Jul 3, 2013
Messages
178
Well pointed out.

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

Cheers
 

luftikus143

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

Watch MrExcel Video

Forum statistics

Threads
1,095,589
Messages
5,445,375
Members
405,327
Latest member
Robweg

This Week's Hot Topics

Top