5 Replies Latest reply on Oct 25, 2016 12:24 PM by Sean Bleything

    Running Sum, restart every time line crosses x axis

    Sean Bleything

      I am trying to figure out how to calculate a running sum of hours that a metric is either positive or negative.  Attached is a workbook that contains a year worth of utility data.  It show when there is an oversupply of energy (positive) and undersupply (negative).    I need to find a way to add up the consecutive hours each time the line is positive, then reset when the line goes negative (and sum those consecutive hours).  I'm not exactly sure how to best visualize the results, but at a minimum, it would be good to know:

      1. what is the longest period of consecutive hours of oversupply?

      2. what is the longest period of consecutive hours of undersupply?

      3. a bin of those results (how many instances of 1 hour of oversupply, 2 hours of oversupply, 3 hours, etc., and the same for negative)


      For extra credit, I also would like to calculate the area under each arc.  Instead of just summing the hours of consecutive oversupply/undersupply, I would like to calculate the total MW hours for each period of oversupply/undersupply.


      I have attached the workbook - can anyone help?