Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Attached Files Attached Files
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. . .".
    Regards
    Don

Posting Permissions

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