1. Time Constraints (Excel 2003)

Hey Everyone,
Attached is a spreadsheet that I have modified with the help of this site.
Is there a way to have the data in B6:B17 only calculate information from Column D for only the last 360 days?
Or do you think this information would be better off in a database?
I am rattaling my brain trying to make this work out for me in the proper manner.
Thanks for every ones help.

2. Re: Time Constraints (Excel 2003)

<P ID="edit" class=small>(Edited by wdwells on 25-Feb-08 14:32. Question added.)</P>The attached file may satisfy your needs. Formula has been added to S8:S50, and the results incorporated into B6:B17. You may wish to hide column S.

Can someone tell me why copying B6 and pasting it into B7:B17, or filling down from B6:B17 results in a #N/A error, while copying the formula from the Formula bar of B6 then pasting it into the Formula bars of B7:B17 (one cell at a time) provides satisfactory results? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

3. Re: Time Constraints (Excel 2003)

Here's a version of Don Wells's solution without needing an intermediate column: enter the following formula in B7:
<code>
=SUMPRODUCT((MONTH(\$D\$8:\$D\$50)=ROW()-6)*\$E\$8:\$E\$50*(TODAY()-\$D\$8:\$D\$50<360))
</code>
and fill down to B18.

4. Re: Time Constraints (Excel 2003)

Don,

If you look closely at the formula in B7:
<code>
=SUMPRODUCT((MONTH(\$D\$8:\$D\$50)=ROW()-6)*\$E\$8:\$E50*\$S\$8:\$S\$50)
</code>
you'll see that it has \$E50 instead of \$E\$50. This causes filling down to go wrong. This is not your fault - it is that way in Big Guy's worksheet.

5. Re: Time Constraints (Excel 2003)

Thank you Hans.
Are you certain that looking closely is sufficient? I looked but didn't see. Something about "there are none so blind. . .".

Posting Permissions

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