3 Replies Latest reply on Sep 14, 2012 12:08 AM by julius golosinda

    Blending data together...I need to add previous non-null value

    Corey Meier



      I have made several attempts to solve this problem, but hopefully there is a solution.


      I have two separate data connections within my workbook.  One data connection contains a daily amount that is generated automatically by the data source (Auto Data).  The other data connection is a manually entered amount (Manual Data).


      My overall goal is to add the amount values from each of the data connections together.  My plan is to utilize data blending based on the date field within the Auto Data connection.


      The problem I want to resolve is that the Manual Data amount is something that does not change too often.  As a result, the end-user would only need to enter an updated amount when it changes (which could within one day or several days).  Ultimately I would like to use the last non-NULL amount value when doing my sum calculation if the manually entered value for that day is NULL...so that I could still trend this value on a daily basis.  I have been trying the LOOKUP command with the offset as well as other function to no avail.


      In my attached packaged workbook, I have setup a worksheet simliar to the table below.  The only difference is that my workbooks Overall_Total_Amount adds a "static" value of 99 if the Manual_Amount value is NULL (which is not what I want to).


      What I want to do is what I have represented within the table below...where the last non-null Manual_Amount is added to the Amount value if the Dates Manual_Amount is NULL.  So on 9/1/12, if the Amount = 195 and the Manual_Amount = NULL, as a result, I want the Overall_Total_Amount = Amount (195) + Last Non-Null Manual Amount (200 -- from 8/30/12) with the result of 395.




      Let me know if you have any questions and thanks in advance for your help.