CODEWITHSUNDEEP
exceldate
Eitan Waks
Eitan Waks

Reputation: 49

Excel, maximum value based on date range

I have an Excel spreadsheet with two columns, the first with dates and the second with values. The dates span over several years. I want to write a function that retrieves the maximum value for each year.

For example, in the 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 for the year 2009 would return the value 3 The function for the year 2010 would return the value 7 The function for the year 2013 would return the value 77

All of the dates are in column A All of the values are in column E In column J I have a list of years needed, i.e. J1 = 2009, J2 = 2010, J3 = 2011, etc.

the function for each year is located in column K corresponding to the year in column J, i.e. the maximum value for 2009 is in cell K1, the maximum value for 2010 is in cell K2, etc.

I believe this function should look something like: =MAX(some type of function that gives me the range of dates per year)

Thanks for all the help

Upvotes: 1

Views: 8600

Answers (3)

Nicolaesse
Nicolaesse

Reputation: 2714

Let's consider this example:

+---+------------+------+-------+-------------+
|   |     A      |  B   |   C   |      D      |
+---+------------+------+-------+-------------+
| 1 | Date       | Year | Value | Max of year |
| 2 | 03/06/2009 | 2009 | 3     | 5           |
| 3 | 05/06/2009 | 2009 | 5     | 5           |
| 4 | 01/01/2010 | 2010 | 7     | 7           |
| 5 | 07/07/2010 | 2010 | 1     | 7           |
| 6 | 01/04/2013 | 2013 | 12    | 77          |
| 7 | 02/05/2013 | 2013 | 77    | 77          |
+---+------------+------+-------+-------------+

You have two choices.

1st solution (easiest)

Add one column named YEAR where you calculate the year of each cell of column A. Then you build a pivot table with max of the column C.

2nd solution (hardest)

Use the matrix function. Add a column B with the year calculated. Then in cell D2 write the following: =MAX(IF(B:B=B2,C:C)) and press CTRL + SHIFT + ENTER. The formula will be transofrmed (into a matrix function) like ={=MAX(SE(B:B=B7;C:C))}. Now you can drag the cell D2 to the end of the column (D7 in the example). You can also use a combination of my second solution with the YEAR function as answered by @Yass.

Upvotes: 0

Eitan Waks
Eitan Waks

Reputation: 49

I managed to answer the question for myself. If I defined the columns corresponding to the dates and values as arrays then I can use the following function:

=MAX(IF(Transaction_Date<=DATE(J1,12,31),IF(Transaction_Date>=DATE(J1,1,1),Account_Balance)))

Upvotes: -2

yass
yass

Reputation: 869

You can use:
=MAX(IF(YEAR($A$2:$A$9)=J1,$E$2:$E$9,0))
Array Formula press Ctrl+Shift+Enter at the same time
You can drag it
$A$2:$A$9 the column of Date $ for absolute references change it to your last row
$E$2:$E$9 the column of values change it to your last row
J1 the first Date in the new column it will change automatically when dragging
If will test the year of the Date to J1 and return the corresponding Value

Upvotes: 0

Related Questions