Results 1 to 6 of 6

20040513, 14:52 #1
 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>

20040513, 15:37 #2
 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 ctrlshiftenter):
E2:
=1SUM(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:
=1SUM(IF((MONTH($A$2:$A$366)=MONTH(A2))*($B$2:$B$366 <>0),($C$2:$C$366)/($B$2:$B$366)))
G2:
=1SUM(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

20040513, 22:12 #3
 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

20040513, 23:54 #4
 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:
=1SUM(IF((INT(($A$2:$A$366$A$2WEEKDAY($A$2)+1)/7)=INT((A2$A$2WEEKDAY($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:
=1SUM(IF((INT(($A$2:$A$366$A$2WEEKDAY($A$2)+2)/7)=INT((A2$A$2WEEKDAY($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

20040514, 17:03 #5
 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).

20040514, 17:53 #6
 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