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

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

I've got a test set of 2 months of daily data. (attached)

In it, I've got two named ranges of data: Date; Value.

I've been using an array formula to find the max. in each month.

For March 2012, it's:

{=MAX(IF((MONTH(Date)=\$H2)*(YEAR(Date)=\$I2),Value))}

where \$H2 is 3 and \$I2 is 2012.

In the screendump (attached), I've highlighted the 2 monthly maximums.

This works well. But now, instead of a MMM-YYYY column, I want to show a column with the date of each monthly max. next to the max. value column.

``Please Login or Register  to view this content.``
Can someone please show me a formula to display this?

Dan.

2. Re: 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

3. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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)

4. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

5. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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

6. Re: From Daily Data, Display Each Month's Maximum Value and its Date

Without support column

E2=IFERROR(AGGREGATE(14,6,Value/(MONTH(Date)=MONTH(\$D2))*(YEAR(Date)=YEAR(\$D2)),1),"")

copy down

7. Re: From Daily Data, Display Each Month's Maximum Value and its Date

Originally Posted by AliGW
What do you mean by "programmatically"? If you want VBA code, we should move the thread for you. Please clarify.
Isn't creating a formula from Excel functions to produce an intended outcome considered to be programming?

Dan.

8. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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?

9. Re: From Daily Data, Display Each Month's Maximum Value and its Date

Ali, I'd prefer a formulaic method.

Dan.

10. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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

11. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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

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

12. Re: From Daily Data, Display Each Month's Maximum Value and its 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.

14. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

15. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

16. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

17. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

18. Re: From Daily Data, Display Each Month's Maximum Value and its Date

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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts