+ Reply to Thread
Results 1 to 15 of 15

return week number based on sum value

  1. #1
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    return week number based on sum value

    Hi All,

    Quick question. Is there a way to return week number in column D based on my orders (column A). For example, if i have 1000 orders listed in column A1:C1000 (variable range). Where column A is the description of the order, Column B is hours taken and Column C is the department.

    I want to sum these orders. If the sum of first few orders equal to 160 hours (broken down by department i.e. 140 hours for production and 20 hours for R&D) then return current week. Move to next orders listed and assign next week number. keep looping it till the end of the sheet.

    I am doing this to forecast my projected delivery week on receipt of orders and when the next order can be made.

    Either vba or excel formula can work. Thank you for your time inadvance.

    Kind Regards,

    week#.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    If this is just a straight sum/160, then try this...
    =INT(SUM($B$2:B2)/160)+1
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: return week number based on sum value

    @ FDibbins ,
    Sorry to "hijack" here , but there were problems recently with E-mail notifications..

    @ shido
    Just incase you did not get Notification, a couple of replies are here for you.
    http://www.excelforum.com/excel-prog...ml#post4186403
    http://www.excelforum.com/excel-prog...ml#post4183412

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    Hi FDibbins,

    i used your formula but i am always getting 1 as my return value.

    However, is there a way to add conditions as i describe in my original post?

    @Alan, thanks, i have replied to original post.

    Thanks to both for your time

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    i used your formula but i am always getting 1 as my return value.

    However, is there a way to add conditions as i describe in my original post?
    Seems to work for me? Below is a copy of your file showing it changing from 1 to 2 at row 27?

    A
    B
    C
    D
    24
    order 23
    6
    R&D
    1
    25
    order 24
    8
    Production
    1
    26
    order 25
    7
    Production
    1
    27
    order 26
    5
    R&D
    2
    28
    order 27
    9
    R&D
    2
    29
    order 28
    7
    Production
    2


    I used =INT(SUM($B$2:B2)/160)+1 in D2, copied down

  6. #6
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    sorry for the mix up. its working. However, is there a way to add condition for department in this formula. 160 hours are broken by department i.e. production hours equals to 140 and R&D hours equals to 20, then return current week number and so on

    Kind Regards

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    OK, i did see that, but those are the only 2 depts you have in your sample. I will add a few others and see what I come up with.

    OK wait. You want to have ONLY up to 140 Prod and 20 RD?

    What happens if we get to 140 Prod, but have not reached 20 RD?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    I have run a number of repertitions, and this gives the same answers as my 1st suggestion, but will only look for prod and RD...
    =INT(SUM(SUMIF($C$2:C2,{"Production","R&D"},$B$2:B2))/160)+1

  9. #9
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    yes, i only need to have two department for look up.

    i want to look up all rows and return current week number if hours for production adds up to 140 and R&D add up to 20 and so on. If either production or R&D department hours donot add up to their assigned hours, then move to another row and look for it. if not found, then assign all hours to either production or R&D.

    Hope i have answered your question.

  10. #10
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    For example,

    If I add up R&D in week 1, it is more than 20 hours. Whereas, production hours didnot match up to 140 hours yet.

    I only want to return week 1 if R&D hours equals to 20 hours, if its more then 20 hours, then return different week.

    Orders Hours Department Week Number
    order 1 9 R&D 1
    order 2 7 R&D 1
    order 3 6 R&D 1
    order 4 6 R&D 1
    order 5 7 R&D 1
    order 6 6 R&D 1
    order 7 8 Production 1
    order 8 5 Production 1
    order 9 8 R&D 1
    order 10 6 R&D 1
    order 11 4 R&D 1
    order 12 9 Production 1
    order 13 7 Production 1
    order 14 6 R&D 1
    order 15 4 R&D 1
    order 16 5 R&D 1
    order 17 5 R&D 1
    order 18 8 R&D 1
    order 19 6 R&D 1
    order 20 6 Production 1
    order 21 5 Production 1
    order 22 9 Production 1

    The above table should look like somthing similar

    Orders Hours Department Week Number
    order 1 9 R&D 1
    order 2 7 R&D 1
    order 3 6 R&D 1
    order 4 6 R&D 2
    order 5 7 R&D 2
    order 6 6 R&D 2
    order 7 8 Production 1
    order 8 5 Production 1
    order 9 8 R&D 3
    order 10 6 R&D 3
    order 11 4 R&D 3
    order 12 9 Production 1
    order 13 7 Production 1
    order 14 6 R&D 4
    order 15 4 R&D 4
    order 16 5 R&D 4
    order 17 5 R&D 5
    order 18 8 R&D 5
    order 19 6 R&D 5
    order 20 6 Production 1
    order 21 5 Production 1
    order 22 9 Production 1

    I hope its clear now

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    OK, I think I have it (I think the week num you have in your sample, 3rd item, should be 2, not 1?)

    Try this in teh 1st row of data, copied down...
    =INT(SUMIF($C$26:C26,C26,$B$26:B26)/IF(C26="R&D",20,140)+1)

  12. #12
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    I am sorry to bother you again

    for some reason, the formula is returning 1 in column D.

    Regards

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    Not sure how you are getting that

    I put your sample answers in D2 down, and then my formula in E2, copied down...
    A
    B
    C
    D
    E
    1
    Orders Hours Department Your Wk My Wk
    2
    order 1
    9
    R&D
    1
    1
    3
    order 2
    7
    R&D
    1
    1
    4
    order 3
    6
    R&D
    1
    2
    5
    order 4
    6
    R&D
    2
    2
    6
    order 5
    7
    R&D
    2
    2
    7
    order 6
    6
    R&D
    2
    3
    8
    order 7
    8
    Production
    1
    1
    9
    order 8
    5
    Production
    1
    1
    10
    order 9
    8
    R&D
    3
    3
    11
    order 10
    6
    R&D
    3
    3
    12
    order 11
    4
    R&D
    3
    3
    13
    order 12
    9
    Production
    1
    1
    14
    order 13
    7
    Production
    1
    1
    15
    order 14
    6
    R&D
    4
    4
    16
    order 15
    4
    R&D
    4
    4
    17
    order 16
    5
    R&D
    4
    4
    18
    order 17
    5
    R&D
    5
    4
    19
    order 18
    8
    R&D
    5
    5
    20
    order 19
    6
    R&D
    5
    5
    21
    order 20
    6
    Production
    1
    1
    22
    order 21
    5
    Production
    1
    1
    23
    order 22
    9
    Production
    1
    1

    E2=INT(SUMIF($C$2:C2,C2,$B$2:B2)/IF(C2="R&D",20,140)+1)
    copied down

  14. #14
    Forum Contributor
    Join Date
    07-08-2010
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: return week number based on sum value

    Hi,

    The issue was probably the file was saved in the temp file. as i saved it in the desktop, it works.

    Thanks for your support.

    Kind Regards

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: return week number based on sum value

    No problem, Im just happy we got you where you wanted to be, and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 28
    Last Post: 07-17-2015, 07:32 AM
  2. [SOLVED] Formula to Return Week Number of Date
    By tdonnelly0987 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2015, 07:34 PM
  3. Week number wants to return using VBA
    By Nisha Dhawan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2014, 05:15 AM
  4. Replies: 5
    Last Post: 09-01-2014, 03:55 PM
  5. Replies: 5
    Last Post: 09-25-2013, 01:48 PM
  6. [SOLVED] Date Formula to Return Week Number in Current Month based on Reporting Period
    By jeversf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 12:10 PM
  7. [SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week
    By amy in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-23-2005, 11:42 AM

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