3 Replies Latest reply on Aug 16, 2018 10:20 AM by Deepika Siriah

    Average calculation

    Deepika Siriah

      Hi,

       

      I need help to calculate average of headcount. I want two monthly average so the columns would be like this:

       

      Headcount                         Avg Headcount

      4013                                   4013        

      4056                                  4035 (4013+4056/2)

      4226                                  4141 (4056+4226/2)

      4329                                  4278 (4226+4329/2)

      4334                                  4332 (4329+4334/2)

      4374                                   4354 (4334+4374/2)

       

       

      Attaching the workbook for your reference too.

       

      Regards,
      Deepika

        • 1. Re: Average calculation
          Ken Flerlage

          Try the following calculation:

           

          IF ISNULL(LOOKUP(SUM([Headcount]),-1)) THEN

              SUM([Headcount])

          ELSE

              (LOOKUP(SUM([Headcount]),-1)+SUM([Headcount]))/2

          END

           

          See attached workbook.

          • 2. Re: Average calculation
            Padma Yalavarthi

            Hi Deepika,

             

            Try the following calculation :

             

            if INDEX()=1 then sum([Headcount])

            ELSE (sum([Headcount])+LOOKUP(SUM([Headcount]),-1))/2

            END

             

             

            Hope it helps!

             

            `

            YP

            1 of 1 people found this helpful
            • 3. Re: Average calculation
              Deepika Siriah

              Hi,

               

              I can take a moving average of this field. E.g the field I received now

               

              Avg Headcount                              Moving Average (New field required)

              4013                                                4013

              4035 (4013+4056/2)                         4024 (4013+4035)/2

              4141 (4056+4226/2)                         4063 (4013+4035+4141)/3

              4278 (4226+4329/2)                        4117 (4013+4035+4141+4278)/4

              4332 (4329+4334/2)                         4160 (4013+4035+4141+4278+4332)/5

              4354 (4334+4374/2)                         4192 (4013+4035+4141+4278+4332+4354)/6

               

               

              Regards,
              Deepika