+ Reply to Thread
Results 1 to 3 of 3

Cumulating Daily Data and Providing a Snapshot

  1. #1
    Registered User
    Join Date
    05-30-2008
    Posts
    64

    Cumulating Daily Data and Providing a Snapshot

    Hi,
    I have a spreadsheet with daily data from the last few years. Each line represents a contract that has been signed, the term in days of the contract and the amount. Im trying to create a chart that shows how much is outstanding for a given day. The problem I have is that the contract terms overlap. eg. a contract was signed 1 Jan for 10 days for $100 and then another on 3 Jan for 15 days. I need to be able to show $200 for 5 Jan.

    I tried doing it manually, but since it is going for a few years, it will take ages and theres not enough room on my spreadsheet. I can only think of 2 manual ways of doing. Horizontally, where each column is a day and i can sum up each day. or vertically, but this will involve going through each line and overwriting the cell with the new contract amounts.

    Does anyone know of an efficient way of doing this?
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Cumulating Daily Data and Providing a Snapshot

    You could use below formula. If you put dates in range E12:En you could use this formula:
    =SUMPRODUCT(--(E12>=A$12:A$50000),--(E12<=A$12:A$50000+B$12:B$50000-1),(C$12:C$50000))
    See attached file where I applied the formula.

    Regards,
    Antonio
    Attached Files Attached Files
    Last edited by antoka05; 06-21-2010 at 05:40 AM.

  3. #3
    Registered User
    Join Date
    05-30-2008
    Posts
    64

    Re: Cumulating Daily Data and Providing a Snapshot

    thanks very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1