4 Replies Latest reply on Mar 19, 2018 8:33 AM by Kerry Page

    Looking for a formula guru - SUM & LOD & MAX

    Kerry Page

      Hi -

       

      I have data that looks at volume at facilities four different ways (yearly avg, 90 day avg, current month average, account level average).  Some facilities can have multiple meters at the same facility.  I need to determine how much collective volume they're doing (sum of meters by facility) looking at 4 different measures and pulling the highest volume number from the 4.  The part I'm struggling with is summing up the individual measure for facilities with multiple meter numbers.

       

      I did a MAX formula (in packaged workbook), and it works great but doesn't account for summing the meters when there are multiple meters under a facility.  So, for facility 11111 it would return a 20 for meter 4444.  I would like it to return a 40 sum of "Past 90 Days Daily Volume") (20 + 4+ 4+ 4+ 4+ 4) for all meters @ that facility, then compare it with the sum of the other 3 columns individually.

       

      This one works but not really - how do I add a SUM calc in here and do I need to do an LOD for FACILITY#? 

      MAX(

      MAX(

      MAX(

      MAX(

      [Yearly Average Daily Volume], [Past 90 Days Daily Volume]),

      [Current Month Daily Volume]),

      [Account level Daily Volume]

      ))

       

      Packaged workbook is attached.  Thank you!

       

      Facility NumberMeter NumberYearly Average Daily VolumePast 90 Days Daily VolumeCurrent Month Daily VolumeAccount level Daily Volume
      111111444410201513
      111111444504010
      1111114446149010
      1111114447241010
      1111114448349010
      111111444944110
      111117445054410
      111118445164610
      111119445274310
      11112044538500410
      11112144549501810
      111122445510502610
      111122445611503510
      1111224457125047710
      111122445813505510