+ Reply to Thread
Results 1 to 6 of 6

Transforming Data from Daily to Weekly

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Transforming Data from Daily to Weekly

    First post. Hope you can help as kindly as the community always does!

    Im trying to change some data from a daily basis to a weekly basis , but I realized that the amount of data is so big that doing it mannualy is going to take forever. I attach here an example of how i am doing it right now. As you can see the in the Weekly Sheet, the Sum(B1:B5) and the Sum(B6:B10) and so forth represent the addition of the week BEFORE so the green highlighted days are Mondays that are NOT included in the sum.
    Is there any way i can automate this process? It is very time consuming. I want the sum to add all the value of the past week (M-F) and show it in a cell that represents the date as monday of the following week.To make it clear:

    Date Daily Value / Weekly Transformation
    Mo1 2 / Mo2 2+3+4+4+7
    Tu1 3 / Mo3 9+5+2+3+4
    We1 4
    Th1 4
    Fr1 7
    Mo2 9
    Tu2 5
    We2 2
    Th2 3
    Fr2 4
    Mo3 1

    Any ideas? I attach the spreadsheet if I did not make my point clear, its pretty hard for me to explain it.
    Thanks in advance guys!
    Sample Daily to Weekly.xlsx

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Transforming Data from Daily to Weekly

    here are 2 ways to solve it

    weekly contains the formula solution

    wk contains the macro solution
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-28-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Transforming Data from Daily to Weekly

    :O Thank you so much! Would you mind explaining me the command a little bit?
    For example for week 1 its =IFERROR(SUM(INDIRECT("daily!B"&MATCH($A1,Daily!$A:$A,0)-5&":B"&MATCH($A1,Daily!$A:$A,0)-1)),0)

    I guess I will need some reading on Match and IFERROR, because ive never used these two formulas before.
    I realized there is a mistake with the formula. Could we stick with the formula solution? Im not very used to using macros ( I still have to learn a lot)

    If you continue dragging the formula downwards the result does not equal to the sum of the past week. Attached you can find the file with the error. The are two sheets, one that calculates weekly values based on the formula and one based on the manual calculation. You will find a comment that shows where the formula makes mistakes (The Zeroes are a sign of mistakes).
    Thanks again
    Viva Guatemala =D
    Sample Daily to Weekly.xlsm
    Last edited by Uberz; 06-29-2013 at 12:53 PM.

  4. #4
    Registered User
    Join Date
    06-28-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Transforming Data from Daily to Weekly

    Hope anyone can help, doing this manually takes hours.
    To make it clear, the formula =SUM(B3:B7) follows a pattern that i can't just drag down because the pattern is NOT =SUM(B4:B8) but it's =SUM(B8:B12).
    The correct pattern should look like this.
    1.=SUM(B3:B7)
    2.=SUM(B8:B12)
    3.=SUM(B13:B17)
    4.=SUM(B18:B22)

    Notice that both numbers increase by 5 every time. Ideas?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    Re: Transforming Data from Daily to Weekly

    With an pivot table.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Transforming Data from Daily to Weekly

    Thank you ! I will study this method

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1