Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with some formulas (Excel 2002)

    I need some help with the attached spreadsheet. Need a formula to calculate WTd, MTD, and Quarterly TAT. After the daily TAT, it all becomes a blurr.......

    Thanks,

    Marie <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need help with some formulas (Excel 2002)

    Are these what you are after? They are all arrays (confirm with ctrl-shift-enter):

    E2:
    =1-SUM(IF((INT(($A$2:$A$366-$A$2)/7)=INT((A2-$A$2)/7))*($B$2:$B$366<>0),($C$2:$C$366)/($B$2:$B$366)))
    This assumes A2 has the first day of the year.

    F2:
    =1-SUM(IF((MONTH($A$2:$A$366)=MONTH(A2))*($B$2:$B$366 <>0),($C$2:$C$366)/($B$2:$B$366)))

    G2:
    =1-SUM(IF((INT((MONTH($A$2:$A$366)-1)/3)=INT((MONTH(A2)-1)/3))*($B$2:$B$366<>0),($C$2:$C$366)/($B$2:$B$366)))

    Copy E2:G2 to E3:G367 for the entire year

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with some formulas (Excel 2002)

    Thanks Steve,

    They didn't quite work the way that I needed them to.

    I am taking another stab at the WTD TAT. This time I am attempting to tie the date back to the week number of the year. hoping that this will provide a calculation for each week individually.

    Any other thoughts/suggestions are always appreciated. Thanks again!

    Marie

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need help with some formulas (Excel 2002)

    If the weeks start on Monday you can use:
    =1-SUM(IF((INT(($A$2:$A$366-$A$2-WEEKDAY($A$2)+1)/7)=INT((A2-$A$2-WEEKDAY($A$2)+1)/7))*($B$2:$B$366<>0),($C$2:$C$366)/($B$2:$B$366)))

    If the weeks start on Sunday you can use:
    =1-SUM(IF((INT(($A$2:$A$366-$A$2-WEEKDAY($A$2)+2)/7)=INT((A2-$A$2-WEEKDAY($A$2)+2)/7))*($B$2:$B$366<>0),($C$2:$C$366)/($B$2:$B$366)))

    The way I did it originally, the week starts on the Jan 1 no matter what the day. NOTE you need analysis toolpack addin attached for Weekday function to work.
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with some formulas (Excel 2002)

    Steve,

    Round 2 did not work, but someone at work created a formula that would work (see attached).

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need help with some formulas (Excel 2002)

    Glad you got it worked out.

    I just wasn't understanding what you were after. I was assuming that all values for a month should be the same, as well as all values for each qtr.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •