+ Reply to Thread
Results 1 to 7 of 7

use arrays to get daily data to weekly

  1. #1
    Registered User
    Join Date
    09-13-2010
    Location
    yonkers,ny
    MS-Off Ver
    Excel 2007
    Posts
    7

    use arrays to get daily data to weekly

    I am new to arrays and found something to convert my daily data to weekly but now can't find it. I want to create a new series ending on Friday.

    can anyone help out?

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: use arrays to get daily data to weekly

    sarar, that's a bit vague. Post a workbook and explain in context.

  3. #3
    Registered User
    Join Date
    09-13-2010
    Location
    yonkers,ny
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: use arrays to get daily data to weekly

    Sorry here is an example attached. I added a column using weekday() then filtered by 6 so I'd have all teh Fridays and pasted to created my new series. BUt I have many more series to apply this too and want to use a formula that I can apply
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: use arrays to get daily data to weekly

    OK, doing this with formulas:

    Enter your first date manually into F3
    Then in F4 use

    =F3+7

    copy down. In G3 use

    =VLOOKUP(F3,A:B,2,FALSE)

    copy down.

    cheers

  5. #5
    Registered User
    Join Date
    09-13-2010
    Location
    yonkers,ny
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: use arrays to get daily data to weekly

    thanks..that works!

    BUT having a problem with having too many rows that have na's now when i fill down that i'll have to clean up. i have 40 series right now to work with. is there a way to do this with a macro that stops or should i just create a macro to clean the data up after its all in weekly format

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: use arrays to get daily data to weekly

    use this in F3 and down (with F2 having the first date manually entered, as above)

    =IF(ISERROR(F3+7),"",IF(F3+7>MAX(A:A),"",F3+7))

    use this in G2 and copy down

    =IF(F3<>"",VLOOKUP(F3,A:B,2,FALSE),"")

    You can fill down the formula to nirwana and won't see any errors.

  7. #7
    Registered User
    Join Date
    09-13-2010
    Location
    yonkers,ny
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: use arrays to get daily data to weekly

    perfect! thank you sooooo much!

+ 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