1 Reply Latest reply on Jul 21, 2015 1:04 PM by Angela Saffin

    Data Allocation Across A Date Range (Trying to Interpret an Excel Equation)

    Douglas Craig
      BACKGROUND

       

      I am buying advertising media for client x.  The media plan (summary of the advertising buy) consists of (simplified for explanation) Media Vendors (e.g NYTimes.com, Google Display Network, RocketFuel, etc), Ad Impressions, Costs and the projected schedule which is laid out on a flowchart.

       

      I have the plan broken out into placements (subsets of the deal with those vendors) that have a specific start date and end date.  Each of those placements have a projected number of ad impressions (An impression is defined as the point in which an ad is viewed once by a visitor, or displayed once on a web page).


      Each placement's  ad impressions should be allocated evenly between it's start end date's.

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      SOLUTION


      I have been able to nearly replicate a traditional media flowchart with the following:


      Column Shelf: Start Date (Day)

      Rows Shelf: Partner (Media Vendor), Placement (where on the website the ad units will run) and Unit Size (The size of the actual ad that is running)

      Size: Calculated Field - DATEDIFF('day',[Start Date], [End Date] +1 )

      Colors - SUM(Impressions)

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      THE PROBLEM


      Though, I can see the "flights" (Date Ranges) of each placement, they allocated all of the impressions against the Start Date itself and not evenly across the date range.


      I need the even allocation so I can provide accurate estimates of advertising activity across Qtrs, Months, Weeks and Days.  I also need to compare the projected activity against the actuals as the advertising activity runs.


      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      THE SOLUTION(?)


      I found this Excel example (that allocates cost across one year based on a monthly basis) that does exactly what I want but I cannot figure out how to replicate it in Excel.  I have attached the workbook in addition to the example below.


      =$G9*((MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0))-(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0))+(EOMONTH(H$8,0)=EOMONTH($C9,0)))

       

       

      It takes the average daily amount ($G9 in the above equation) and calculates it against:

       

      • Start Date
        • End of the Month - Start Date to return all of the days that activity has run
        • minus
        • End of last Month - Start Date to isolate all of the activity that has run prior to this month
      • End Date
        • Essentially the same but to account for any days if the activity date ended during the month being calculated.
      • +1
          • To provide for the day missing from the first calculation
        • Allow me to reference a specific month
        I am stuck on the reference to the Monthly reference in the equation.   The data has been import as a total, with the date ranges designating how it should be distributed.   Is there a way to write a calculation that will
        • Allow me to reference a specific month
        • Reference the last day of that month to properly calculate the total days of activity for that month?