Results 1 to 6 of 6

Offset weeks with multiple criteria.

Threaded View

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    48

    Offset weeks with multiple criteria.

    Hi

    I'm trying to find a way to look up some weeknumbers based on a set weeknumber and year. My data crosses into the next year and i always need to compare data from 16 weeks in the past with an extra offset of 2 weeks.

    In the attached file you'll see a basic setup what i want to achieve. In column A to C there is raw data to work with. Column G and H is where i set the year and week. And in column L to P is where i need to load in the weeks with the extra offset of two weeks. So i have set week 4 and need to get week 2 in column P.

    I have did get the normal offset based on criteria to work but as soon as it crosses over into the new year i get negative values instead of week 51. This is basically how for i got.
    The names in the formula are different from the example, but i hope you get my point.

    =OFFSET(INDEX('Data.xlsb'!GS_WEEK;MATCH(SUMPRODUCT(--('Data.xlsb'!GS_WEEK=$C$4)*(--('Data.xlsb'!GS_YEAR=$C$3))*(--('Data.xlsb'!GS_DAY="Mon"));'Data.xlsb'!GS_WEEK);'Data.xlsb'!GS_WEEK));-14;0)

    I hope you can help get my weeks right.
    Thanks!
    Attached Files Attached Files

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