+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,082

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,082

    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. #4
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

    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.
    Last edited by AliGW; 04-13-2020 at 03:39 AM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    750

    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. #7
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Quote Originally Posted by AliGW View Post
    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. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    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. #9
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Ali, I'd prefer a formulaic method.

    Dan.
    Last edited by AliGW; 04-13-2020 at 07:47 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    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. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    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. #12
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Quote Originally Posted by AliGW View Post
    =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.
    Last edited by dosdan; 04-13-2020 at 07:26 PM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

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

    No worries.

  14. #14
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

    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.
    Attached Files Attached Files
    Last edited by dosdan; 09-28-2020 at 12:01 AM.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  16. #16
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Quote Originally Posted by JeteMc View Post
    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.
    Attached Images Attached Images
    Last edited by dosdan; 09-30-2020 at 09:19 PM.

  17. #17
    Registered User
    Join Date
    04-04-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    8

    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.
    Attached Images Attached Images
    Last edited by dosdan; 09-30-2020 at 10:07 PM.

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 02-19-2019, 11:37 PM
  2. Replies: 5
    Last Post: 01-24-2019, 05:30 PM
  3. Replies: 2
    Last Post: 08-24-2016, 05:52 AM
  4. [SOLVED] pick end of month date from daily date
    By mohin306 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2015, 06:44 PM
  5. [SOLVED] display total sales at the end of each month from date-wise data
    By Ranjeet2001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-11-2014, 01:20 AM
  6. Month to Date and Quarter to Date Average from Daily data
    By asvanthi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2014, 07:10 AM
  7. [SOLVED] Find Daily Maximum from Hourly Data
    By computing in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2013, 02:29 PM

Bookmarks

Posting Permissions

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