Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Aggregate daily data to new sheets monthly, quarterly, yearly

    Dear Experts !
    I have a long excel sheet data for many years on daily basis. I need to sum this data on monthly ,quarterly and yearly basis in 3 separate sheets?

    Thanks
    Attached Files Attached Files
    Last edited by farrukh; 2014-08-29 at 04:13.

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You could use vba or sumproduct formulas. Provide file to dguillett@gmail.com

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi
    I have attached the file sum columns needs to get weekly sum, some get weekly average .Same for Monthly and Yearly.
    My week always starts from Thursday to Wednesday

  4. #4
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sorry correction !

    Require 4 sheets
    Weekly
    Monthly Quartely
    Yearly.

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    After looking at the file, I see NO reason to have separate sheets when a good custom autofilter macro to get the required data will do just fine.
    So, you need to autofilter and then apply your custom filter to get the data and just make a report on a separate sheet or even at the top of the data sheet

    ActiveSheet.Range("$CP$4:$EI$371").AutoFilter Field:=1, Criteria1:=Array( _
    "="), Operator:=xlFilterValues, Criteria2:=Array(1, "1/31/2013", 1, _
    "2/28/2013", 1, "3/31/2013")
    And then use subtotal at the top with the visible option 109 vs 9
    =SUBTOTAL(109,(F11:OFFSET(F11,lastrow,0)))

  6. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Thank for your reply
    Actually I have years of data every time filtering the data may do the human mistake and get the wrong figures. Is it any way to get data in different sheets
    Weekly,Monthly,Quartely and yearly ?

  7. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Of course, what you want done can be done but it is still not the best. Try the attached file
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Dear

    While Google I search one file from forum which is attached . It perfectly works according to my needs but I face problem in Average if vales starts from without decimal the average works fine but when example I need to average 0.21 values like the average not correct .

    Any one help me out

    {=AVERAGE(IF(YEAR($A$5:$A$64)=YEAR(D5),IF(MONTH($A $5:$A$64)=MONTH(D5),$B$5:$B$64)))}
    Attached Files Attached Files
    Last edited by farrukh; 2014-08-31 at 02:33. Reason: {=AVERAGE(IF(YEAR($A$5:$A$64)=YEAR(D5),IF(MONTH($A$5:$A$64)=MONTH(D5),$B$5:$B$64)))}

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand the logic to get 0.21 as the average. The average of the values from B5:B15 is only 0.17. You can get this average with the array formula (confirm with ctrl-shift-enter):
    =AVERAGE(IF((YEAR($A$5:$A$64)=YEAR(D5))*(MONTH($A$ 5:$A$64)=MONTH(D5))*ISNUMBER($B$5:$B$64),$B$5:$B$6 4))

    This formula ignores the blank cells, which your formula does not.

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2014-09-02)

  11. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Mistake was in going down to row 64 which goes into Feb. Another way would be to make a defined name for each month and just average that.

  12. #11
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    Thank you very much formula you provided works perfectly !

    Sorry due to Writing English I mean that value when we put like in decimal points e.g ( 0.21, 0.2, ) the formula not works showing is incorrect results.


    Thank you.

  13. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This approach does 2 things. Bloats the file with MANY unnecessary formulas and doesn't address the weekly desire. I still recommend you filter..... UMMV

Posting Permissions

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