# Thread: Need help with some formulas (Excel 2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•