Value of the first day of a year in a list
 

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

Could you explain why those those dates would/should result in those particular values? It would make it easier to understand what you're trying to accomplish.

Thanks.

S.

Value of the first day of a year in a list
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Value of the first day of a year in a list
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Value of the first day of a year in a list - Capture.PNG (0/1)
 

On Sat, 2 Jun 2012 17:19:10 +0000, Guym wrote:


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

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 the correct value. Any hint? Thank you again guys.

Spencer,

Findin a stock's first and last values for each year will yield the annual return. So far Claus solution works fine for the first value of a year. Now can't find a formula for the last value.

Value of the first day of a year in a list
 

Hi Guy,

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

One last thing. Changing "true"
with "false" to return the last value of a year does not return the
correct value. Any hint? Thank you again guys.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hey Claus,

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

Guy