Value of the first day of a year in a list

[Guym]

I would like excel to return the value of the first day of each year i
a column.

Assume the table below:
----A ------------------B
1 Date----------------Value
2 june 5 2000-----------82
2 august 8 2000--------120
3 december 11 2000----456
4 march 4 2002----------75
5 september 29 2002----965

The formula should return the values 82 for the year 2000 and 75 for th
year 2002. The formula will be helpful as I have a list of thousand
data over 20 years.

Thank yo

 

[Claus Busch]

Hi Guy,

Am Sat, 2 Jun 2012 17:19:10 +0000 schrieb Guym:

I would like excel to return the value of the first day of each year in
a column.

Assume the table below:
----A ------------------B
1 Date----------------Value
2 june 5 2000-----------82
2 august 8 2000--------120
3 december 11 2000----456
4 march 4 2002----------75
5 september 29 2002----965

if your dates are sorted, the first day of each year is the first value
of this year. Then try for year 2000:
=INDEX(B:B,MATCH(TRUE,YEAR($A$1:$A$2000)=2000,0))


Regards
Claus Busch

 

[Claus Busch]

Hi Guy,

Am Sat, 2 Jun 2012 22:20:08 +0200 schrieb Claus Busch:

=INDEX(B:B,MATCH(TRUE,YEAR($A$1:$A$2000)=2000,0))

this is an array formula to enter with
CTRL+Shift+Enter


Regards
Claus Busch

 

[Ron Rosenfeld]

I would like excel to return the value of the first day of each year in
a column.

Assume the table below:
----A ------------------B
1 Date----------------Value
2 june 5 2000-----------82
2 august 8 2000--------120
3 december 11 2000----456
4 march 4 2002----------75
5 september 29 2002----965

The formula should return the values 82 for the year 2000 and 75 for the
year 2002. The formula will be helpful as I have a list of thousands
data over 20 years.

Thank you

You could use a Pivot Table.

Select your table (or, if it is contiguous, and separated from the rest of the worksheet by blank cells, just a single cell within the table):
Insert/Pivot Table
Drag Date to Row Labels; Drag Value to Values

Edit the Values to show Min of Value instead of Sum
Right click on the Row Labels; select to Group By years.
Format to taste

-->

Years Values
2000 82
2002 75

 

[Guym]

Hi Guy,

Am Sat, 2 Jun 2012 22:20:08 +0200 schrieb Claus Busch:
-

this is an array formula to enter with
CTRL+Shift+Enter


Regards
Claus Busch

Thank you Claus. It works. That's great. One last thing. Changing "true
with "false" to return the last value of a year does not return th
correct value. Any hint? Thank you again guys

 

[Guym]

Hi Guy,

Am Sun, 3 Jun 2012 02:48:56 +0000 schrieb Guym:
-

the last value of the year is 1 row above the first value of the
following year. If you have continuous years, try this for year 2000:
=INDEX(B:B,MATCH(TRUE,YEAR($A$1:$A$2000)=2001,0)-1)


Regards
Claus Busch

Hey Claus,

Thank you so much for your help. It works just fine.

Gu