Min, Max Values by Calendar Year

[skywriter]

I have a spreadsheet with data that spans from Jan. 1, 2017 to the present.
In 1 column there is a date entry for every date since then.
In another column I have values.
I would like formulas to get the following.

#1 The minimum and maximum values present for each calendar year, the dates for those values and if that value is repeated during that calendar year just the date that it last occurred.

#2 For this year and future years I would like to be able to have a dynamic way of keeping track of the min and max to date and the last time they occurred if it's a repeated min or max value.

And if you are wondering yes my Excel version is 2016.

Thank you in advance.

[Pete_UK]

All of the following formulae are array formula, so you will have to commit them using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter. Use this one in E9:

=MIN(IF(YEAR($A$2:$A$21)=$D9,$B$2:$B$21))

Copy it into G9 and change the MIN to MAX at the beginning. Then you can use this formula in F9:

=INDEX($A$2:$A$21,MAX(IF(($B$2:$B$21=E9)*(YEAR($A$2:$A$21)=$D9),ROW($B$2:$B$21)-1)))

and copy it into H9 (no need to make any changes). As you clearly have more data than in your sample, you will need to change the ranges - you can do this using Find & Replace to change $21 to $_last_row.

Don't forget to use CSE if you make any changes to the formulae.

Hope this helps.

Pete

[skywriter]

Thanks for the heads up on the cse.
I was hoping to have formulas where I don't enter ranges like A1:A35 as an example because I would have to select 365 cells for each year.
I was hoping to be able to use whole columns like A:A and point to a cell with a year and have the formula figure out where the cells with the appropriate year's dates are.


Thanks for your efforts.

[Pete_UK]

Sorry, I forgot to say that once you have the formulae in cells E9:H9 (with the full range of data), then you can just copy the four formulae down as far as you need them. The formulae are looking for the year in column D, so you don't need to change each range to suit each year's worth of data.

You can change the $21 to $5000, say, for the formulae in row 9, and then copy them down, and this will cover enough data for 14 or so years.

You shouldn't use full-column references with array formulae, as each cell in the range will be calculated (over 1 million cells each time), and so will tend to become very sluggish.

Hope this helps.

Pete

[skywriter]

Okay I'll extend the references.

I thought about something else that might be an issue but then neglected to mention it.

The cells with the years 2017 etc. are actually the first day of each of those years formatted to look like just the year.

I'm using them in other formulas and they're causing an issue with your suggested formulas.

When I change them to just the year all is fine but I already have formulas dependent on them being the first day of the year.

Can you suggest a modification to your formula?

Thanks for your time.

[leelnich]

Hi all - Some non-array options:

In E9: =AGGREGATE(15,6,$B$2:$B$5000/(YEAR($A$2:$A$5000)=YEAR($D9)),1)
In F9: =LOOKUP(2,1/($B$2:$B$5000=E9)/(YEAR($A$2:$A$5000)=YEAR($D9)),$A$2:$A$5000)
In G9: =AGGREGATE(14,6,$B$2:$B$5000/(YEAR($A$2:$A$5000)=YEAR($D9)),1)
In H9: =LOOKUP(2,1/($B$2:$B$5000=G9)/(YEAR($A$2:$A$5000)=YEAR($D9)),$A$2:$A$5000)

If you want to copy them down to future years, they can be wrapped in the IFERROR() function to return "" until the data catches up.
In E9: =IFERROR(AGGREGATE(15,6,$B$2:$B$5000/(YEAR($A$2:$A$5000)=YEAR($D9)),1),"")

Note- I did try these with full-column references. As Pete predicted, they were fairly slow to calculate.

Ps - These have been modified to comply w/ post #5

[Pete_UK]

Okay I'll extend the references.

I thought about something else that might be an issue but then neglected to mention it.

The cells with the years 2017 etc. are actually the first day of each of those years formatted to look like just the year.

I'm using them in other formulas and they're causing an issue with your suggested formulas.

When I change them to just the year all is fine but I already have formulas dependent on them being the first day of the year.

Can you suggest a modification to your formula?

Thanks for your time.

If D9 contains a date rather than just the year, then instead of referring specifically to $D9, you can use YEAR($D9) instead. Change this in the 4 formulae in row 9 (using CSE after each change), then copy down.

NOTE that although the AGGREGATE function (as used by leelnich) does not need to be committed using CSE, it is still an array function, as it will check every cell within the range(s), so it is still not a good idea to use full-column references.

Hope this helps.

Pete

[skywriter]

YEAR($D9)

Okay duh, I should have figured that out for myself but I decided to do this right after I woke up.

All is good and working beautifully now.

Another issue I had was the dates were off by a day but after a cup of coffee I realized it was because my actual data starts on row 3 instead of 2 as in my sample so I was able to figure that one out by myself as a -2 instead of -1 one fixed it.

Thanks for your help, it is much appreciated.

[Pete_UK]

Glad to help, and thanks for the rep.

Pete

[Czeslaw]

Formulas
Value

Please Login or Register  to view this content.

Date

Please Login or Register  to view this content.