5 Replies Latest reply: Oct 11, 2013 2:18 PM by Chadd McNicholas RSS

    RangeAvg with Above/Below getting strange results

      UPDATE per discussion belowThe functions are behaving as advertised for Above() and Below().  However the examples used in the HELP docs for the various Range functions are incorrect when using negative numbers with Above().  Apparently I'm not the first person to encounter this issue.  The Help Docs for Range functions need to be corrected.

       

       

      Hi all,

       

      I'm trying to calculate a 7-day moving average for a specified Cost.  In this case, I actually want to take the average of the current day and three previous days and three subsequent days.  Reading up on RangeAvg, this seemed rather trivial, but I am not achieving the expected results.

       

      So to calculate the moving average, I would just need to do either of the following:

      =rangeavg(below(sum(totalcost),-3,7)) ------- This should take the RangeAvg of the totalcost starting at 3 above and include through 3 below the current row.  What I actually see is the average of the 7 rows below starting 9 rows above.


      The other option is to use the above function:

      =rangeavg(above(sum(totalcost),-3,7)) ------- This should take the RangeAvg of the totalcost starting at 3 below and include through 3 above the current row. What I actually see is the average of  the 7 rows above starting 9 rows below.


      I tried a couple other combinations of numbers to get the rows right, but nothing seemed to work.  I'm pretty sure I'm missing something fundamental here.  Please help


      Data Set:

      LOAD * INLINE [
      Date, totalcost, Expected
      01/01/2013, $30.87,
      01/02/2013, $78.66,
      01/03/2013, $71.44,
      01/04/2013, $42.00, $53.40
      01/05/2013, $37.44, $55.33
      01/06/2013, $56.24, $49.06
      01/07/2013, $57.12, $41.99
      01/08/2013, $44.38, $38.12
      01/09/2013, $34.80, $36.22
      01/10/2013, $21.92, $32.25
      01/11/2013, $14.95, $29.37
      01/12/2013, $24.12, $26.84
      01/13/2013, $28.44, $28.24
      01/14/2013, $37.00, $32.63
      01/15/2013, $26.64, $39.73
      01/16/2013, $44.59, $41.01
      01/17/2013, $52.70, $43.68
      01/18/2013, $64.60, $48.48
      01/19/2013, $33.12, $59.52
      01/20/2013, $47.09, $62.00
      01/21/2013, $70.62, $58.36
      01/22/2013, $103.95, $56.89
      01/23/2013, $61.94, $60.85
      01/24/2013, $27.18, $63.61
      01/25/2013, $54.34, $61.85
      ]
      ;

       


      Output:

      DatetotalcostExpected average

      =rangeavg(below(

      sum(totalcost),-3,7))

      =rangeavg(above(

      sum(totalcost),-3,7))

      01/01/2013$30.87-$41.99
      01/02/2013$78.66-$38.12
      01/03/2013$71.44-$36.22
      01/04/2013$42.00$53.40$30.87$32.25
      01/05/2013$37.44$55.33$54.77$29.37
      01/06/2013$56.24$49.06$60.32$26.84
      01/07/2013$57.12$41.99$55.74$28.24
      01/08/2013$44.38$38.12$52.08$32.63
      01/09/2013$34.80$36.22$52.78$39.73
      01/10/2013$21.92$32.25$53.40$41.01
      01/11/2013$14.95$29.37$55.33$43.68
      01/12/2013$24.12$26.84$49.06$48.48
      01/13/2013$28.44$28.24$41.99$59.52
      01/14/2013$37.00$32.63$38.12$62.00
      01/15/2013$26.64$39.73$36.22$58.36
      01/16/2013$44.59$41.01$32.25$56.89
      01/17/2013$52.70$43.68$29.37$60.85
      01/18/2013$64.60$48.48$26.84$63.61
      01/19/2013$33.12$59.52$28.24$61.85
      01/20/2013$47.09$62.00$32.63$47.82
      01/21/2013$70.62$58.36$39.73$40.76
      01/22/2013$103.95$56.89$41.01$54.34
      01/23/2013$61.94$60.85$43.68-
      01/24/2013$27.18$63.61$48.48-
      01/25/2013$54.34$61.85$59.52-