Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
Thanks. S. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Quote:
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
|
|||
|
|||
Quote:
Thank you so much for your help. It works just fine. Guy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert day of year to numeric value format year+day in 4 digits | Excel Worksheet Functions | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
List First Friday's Date of the year | Excel Discussion (Misc queries) | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel |