3 Replies Latest reply on Jul 25, 2018 7:33 AM by Ankit Bansal

    Average numbers with values and nulls

    Jeff Stanley

      I have the data below, and I need to be able to average the values that are not null for a series of daily data - in excel the average function would ignore the nulls, I could also use a countif function to only use values greater than zero or not null

       

       

       

      Account Number888
      Date of SettleTotal Exposure
      3/1/201850
      3/2/2018
      3/3/2018100
      3/4/2018
      3/5/201875
      3/6/2018
      3/7/2018
      3/8/2018
      3/9/2018100
        • 1. Re: Average numbers with values and nulls
          Ankit Bansal

          Jeff,

           

          Do you mean to say you want to consider null values also when finding average?

           

          if yes then you can use avg(ifnull(total_exposure,0))

           

          and if you don't want to consider nulls then that is default behavior. You need not do anything, simple average will work.

           

          Thanks,

          Ankit

          1 of 1 people found this helpful
          • 2. Re: Average numbers with values and nulls
            Jim Dehner

            hi jeff - see the attached - there are 3 ways shown

            one is just using avg in the total

            a second uses and lod with a count of the marks to create and avg

            the third just uses a straight LOD

            the return this

             

            the advantage of the lod's is you can remove the date field

             

            note with the lods you would need to account for the Account Number

            like this

             

            ({ FIXED [account number] :avg([Total Exposure])})

             

             

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Average numbers with values and nulls
              Ankit Bansal

              In tableau also you can use similar to countif like:

               

              sum(total_exposure) / count(if total_exposure>0 then total_exposure end)

               

              in count , nulls will anyway not be counted by default.