Excel, maximum value based on date range -
i have excel spreadsheet 2 columns, first dates , second values. dates span on several years. want write function retrieves maximum value each year.
for example, in following data set: june 3, 2009 3 june 5, 2009 5 january 1, 2010 7 july 7, 2010 1 april 1, 2013 12 may 2, 2013 77
the function year 2009 return value 3 function year 2010 return value 7 function year 2013 return value 77
all of dates in column of values in column e in column j have list of years needed, i.e. j1 = 2009, j2 = 2010, j3 = 2011, etc.
the function each year located in column k corresponding year in column j, i.e. maximum value 2009 in cell k1, maximum value 2010 in cell k2, etc.
i believe function should like: =max(some type of function gives me range of dates per year)
thanks help
you can use:
=max(if(year($a$2:$a$9)=j1,$e$2:$e$9,0))
array formula press ctrl+shift+enter @ same time
can drag it
$a$2:$a$9 column of date $ absolute references change last row
$e$2:$e$9 column of values change last row
j1 first date in new column change automatically when dragging
if test year of date j1 , return corresponding value
Comments
Post a Comment