+ Reply to Thread
Results 1 to 5 of 5

Sum based on Current Week, Previous Weeks vs. Current Week Forward

  1. #1
    Registered User
    Join Date
    04-29-2008
    Posts
    44

    Question Sum based on Current Week, Previous Weeks vs. Current Week Forward

    I have attached an example file similar to the file I am working with, just with sensitive data removed. Each week has multiple columns within it. I need to set a formula that automatically looks at each week before the current week (based on weeknum()-1 to convert from the standard week in Excel to actual ISO week) and sums only the quantities in a specific cell within each week, and I need to set a formula that automatically looks at each week following the current week (and including the current week) that sums only the quantities in a specific cell within those weeks.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,359

    Re: Sum based on Current Week, Previous Weeks vs. Current Week Forward

    Quote Originally Posted by cjsec9 View Post
    ...each week before the current week...and sums only the quantities in a specific cell within each week, and...each week following the current week (and including the current week) that sums only the quantities in a specific cell within those weeks.
    Which cells?

    BTW what is an "actual ISO week"?

    As an example to get the discussion going, suppose you want to sum Order Qty in weeks prior to current week. In F36 use this formula and copy to all other weeks.

    =IF(G1>=TODAY()-WEEKDAY(TODAY(),2)+1,"",SUM(F3:F35))
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-29-2008
    Posts
    44

    Re: Sum based on Current Week, Previous Weeks vs. Current Week Forward

    1) There are notes in the example file (EA3, ED3, EE3, EF3) that indicate which cell types need to be summed. In this case, it is a single sub-column of each week for each week.
    2) ISO weeks are a standard week numbering system used for fiscal year timekeeping. More on ISO weeks can be found here (Wikipedia). Excel, using weeknum(), shows the week as one week more than the actual current ISO week.

    The example file shows only a very small portion of the main file I'm working with. Below the type of section shown is another section, and another section below that, both of which are irrelevant to the question, but still exist. I need to be able to have the formulas on the right side of the project so that a quick glance can show me one sum for all past weeks for that particular line-item, and one sum for the current week forward for that same line-item.

    Thanks

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,359

    Re: Sum based on Current Week, Previous Weeks vs. Current Week Forward

    The way the data is organized makes this very difficult to do. I couldn't figure out a direct way to do this with formulas. I added intermediate results in rows 37-44 for each week and the results at the right edge total those intermediate results. However, I don't know if that approach is compatible with how the rest of your data is organized.

    (The way that I would have organized this is to add a column for start of week date and a column for week number, then extend the data downward instead of across. That allows all kinds of analysis to be easily done with pivot tables, or simple formulas.)

    To calculate past weeks vs. future weeks does not require a conversion to/from ISO week numbers, because the dates themselves are provided for each week.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-29-2008
    Posts
    44

    Re: Sum based on Current Week, Previous Weeks vs. Current Week Forward

    I've created a new layout that might be able to help solve the problem. I need the data horizontally, not vertically.

    The "projected" and "actual" tables will just be data, grabbing their numbers with simple =CELL formulas. The formulas I need would go in columns BL, BM, BO, and BP. For example, column BL3 needs to have a formula that looks at the "projected" table for that particular line (A3:AD3) and, based on the week number above it, only sums that week and each prior week. BM3 would need to have a formula that looks at the "projected" table for that line (A3:AD3) and, based on the week number above it, only sums that week and each following week. The formula needs to be standard so that no maintenance is required throughout the year, it can just be a set it and forget it kind of thing. I've considered using LOOKUP, but i can only return one value. I'm not sure how to make it intelligent enough to look at the range of weeks above the table, look at the specific week above the formula, and add everything before or everything after+that week.
    Attached Files Attached Files

+ 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