From Daily Data, Display Each Month's Maximum Value and its Date

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

1. I would avoid naming ranges with terms that might already be used in standard excel functions - =DATE is a legacy excel function

2. Your formula in E2 was not ARRAY entered

3. Try this instead...
=MAX(IF((Date > =D3)*(Date < EDATE(D3,1)),Value))
ARRAY entered
(I did not mess with your named ranges)

Thanks for that, it's a more elegant solution than mine. (Elegance is nice.)

Any idea how to programmatically get the matching date for each month's maximum value?

Dan.

What do you mean by "programmatically"? If you want VBA code, we should move the thread for you. Please clarify.

No, it isn't. Using VBA is coding, because it uses a programming language (Visual Basic for Applications).

Using a combination of Excel functions, logical and other operators is simply constructing formulae.

You answered my question with a question, so that rather suggests you think I shouldn't have asked.

Presumably, then, you are happy with any solution?

Ali, I'd prefer a formulaic method.

Dan.

OK - that's clear. So, have you tried the method offered in post #6? Let us know and we can take it from there.

If I have understood correctly that you want the date, try this:

=LOOKUP(2,1/((Date > =D2)*(Date < EDATE(D2,1))*(Value=E2)),Date)

Originally Posted by AliGW

=LOOKUP(2,1/((Date > =D2)*(Date < EDATE(D2,1))*(Value=E2)),Date)

Ali, thanks for that. I had resorted to including a combined MMYY&Value column (e.g. "031226.16") in front of the original Col A, and then using:

=VLOOKUP(TEXT(E2,"mmyy")&F2,A:B,2,FALSE)

But your method is simpler.


Dan.

No worries.

A variation on this topic.
Excel 2010.


Cols A & B: Dates, Generated

Dates is consecutive daily dates, 21 months long. (The actual list I will be using is over 8yrs long, but I've shortened it in the attached sheet.)
Generated is a list of Solar PV daily generated energy values.
I also have an integer list (1-20) called Ranking

Using the formula below, I can get the top 20 generated values from all the September days over the years:

=LARGE(IF(MONTH((Date)=9),Generated),Ranking)

Please Login or Register  to view this content.

1. How do I add a formula in the column next to the Top 20 report to also show the year each of these September high-values occurred in?
2. How would I add a formula in Col C to show the ranking of this particular Generated value within all generation for the same month over all the years? For example:
   A277 is 1-Dec-2012
   B277 is 29.66
   C277 is 33, indicating that B277 contains the 33rd highest December (month selection based on the month in A277) daily generation.

As for #1
The formula in column F needs to be modified to read: =LARGE(IF(MONTH(Dates)=9,Generated),E2) and also needs to be array entered**.
An alternative formula (column G) that would not need array entering is: =AGGREGATE(14,6,Generated/(MONTH(Dates)=9),E2)
The formula to get the date in which the high value occurred is: =INDEX(Dates,AGGREGATE(15,6,(ROW(Dates)-ROW(A$1))/(MONTH(Dates)=9)/(Generated=F2),1))
Note that to show only the year custom format H2:H21 yyy
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

As for #2 paste the following into cell C2 and double click the fill handle to copy down: =SUMPRODUCT((MONTH(Dates)=MONTH(A2))*(Generated>B2))+1
Note that a pivot table, as modeled on the right side of the sheet, might produce a better visualization.

Let us know if you have any questions.

Originally Posted by JeteMc

As for #1
[1] The formula in column F needs to be modified to read: =LARGE(IF(MONTH(Dates)=9,Generated),E2) and also needs to be array entered**.
...
[2] The formula to get the date in which the high value occurred is: =INDEX(Dates,AGGREGATE(15,6,(ROW(Dates)-ROW(A$1))/(MONTH(Dates)=9)/(Generated=F2),1))
...
[3] As for #2 paste the following into cell C2 and double click the fill handle to copy down: =SUMPRODUCT((MONTH(Dates)=MONTH(A2))*(Generated>B2))+1

Thank you for that help, JeteMc.

1. I thought it would take an array formula. I tried an array formula similar to this but didn't get the answer I expected. so I abandoned it. I should have looked at the results more carefully.

2. Why do you use ROW(A$1)? This will always be 1.

3. I suspected that, when implemented, this column would look too busy. So I've added a bit more code to only show Top-20s in this column. Jul-4 means that this particular July day's generation was the 4th highest July day ever.
=IF(SUMPRODUCT((MONTH(Dates)=MONTH(A2))*(Generated>B2))+1<=20,TEXT(MONTH(A2)*29,"mmm")&"-"&SUMPRODUCT((MONTH(Dates)=MONTH(A2))*(Generated>B2))+1,"")

I searched for and found an interesting way to show month number as mmm: multiply it by 29 in TEXT(MONTH(A2)*29,"mmm"). A bit of experimenting shows that TEXT(MONTH(nnn),"mmm") treats nnn as DOY, with 366 showing as Dec (must take the current year's leap-year status into account), while 367 wraps-around to Jan. So when you multiply 1-12 by 29 the product must wrap-around to fall unambiguously into the correct monthly bounds.

Dan.

JeteMc, the problem with =INDEX(Dates,AGGREGATE(15,6,(ROW(Dates)-1)/(MONTH(Dates)=9)/(Generated=F2),1)) is that it can't handle days with the same generation and month-of-the year, but with different years. So for example, in the screengrab below, #13 & #14, both with 24.21, show the same date, 23/09/2016, whereas #14 should be 15/09/2020.

=IF(SUMPRODUCT((MONTH(Dates)=MONTH(A3))*(Generated>B3))+1<=20,TEXT(MONTH(A3)*29,"MMM")&"-"&SUMPRODUCT((MONTH(Dates)=MONTH(A3))*(Generated>B3))+1,"") does show them correctly as both Sep-14. Ideally when there's a tie like this, Sep-15 and #15 in the Best September days should be skipped, but that's probably getting too complex.

Dan.

As to point #2 in post #16, you could replace ROW(A$1) with 1. The purpose is to renumber the array produced by ROW(Dates) so that it starts with 1 instead of 2. Utilizing the Evaluate Formula feature on the Formulas tab may help illustrate this.
As to post #17, you might also want to consider the following as applied to column H in the file attached to post #15:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.