3 Replies Latest reply on Jan 13, 2017 11:46 AM by Jamieson Christian

    adding column that have null values

    dipti.patel.2

      Hello,

       

      i have to create a calculated field that adds up a few columns but some of the rows have null values for those measures so when I add then using the below formula I get the calculated values but do not want to show 0 where there are null for all the calculated columns but rather just show blank or null.

       

      Look at the highlighted below in the Total Spending I dont want to see 0 nut want to see null or blank.

        • 1. Re: adding column that have null values
          Jamieson Christian

          Dipti,

           

          Without seeing your workbook, here are a couple ideas:

           

          IDEA 1 — Only return a value if the inputs contain at least one non-null (implicitly returns NULL if all the inputs are NULL):

           

          [Total Spending]

          IF NOT ISNULL(LOOKUP(SUM([Disbursements]),0)) OR NOT ISNULL(LOOKUP(SUM([Obligations]),0)) OR NOT ISNULL(LOOKUP(SUM([Expenditures]),0)) THEN
               IF(ISNULL(LOOKUP(SUM([Disbursements]),0))) THEN 0 ELSE SUM([Disbursements]) END +
               IF(ISNULL(LOOKUP(SUM([Obligations]),0))) THEN 0 ELSE SUM([Obligations]) END +
               IF(ISNULL(LOOKUP(SUM([Expenditures]),0))) THEN 0 ELSE SUM([Expenditures]) END
          END

          IDEA 2 — Format the field with a custom number format that treats 0 as blank (only works if 0 will always represent "no data")

           

          Number format:

          #,##0;-#,##0;""

           

          1 of 1 people found this helpful
          • 2. Re: adding column that have null values
            dipti.patel.2

            Thank you for your help

             

            #1 worked like charm

             

            for #2 i did format the number as shown by you but it does not replace the 0 by "",, was wondering how does it know that it shld replace the 0 by "" in the format specified

            • 3. Re: adding column that have null values
              Jamieson Christian

              Dipti,

               

              Huh. Should have worked. In my tests it worked.

               

              Basically, in a custom number format, you can specify three different ways to format the number: positive values, negative values, and zero value (in that order). Each formatting string is separated by semicolons. In my example, the last one is simply the empty string, denoted by 2 double quotes ""

               

              Not sure why it didn't work for you. But I looked at the screenshot of your data again, and it looks like you do have situations where a 0 does not represent a lack of data, so you probably wouldn't want to using the formatting trick anyway.

              1 of 1 people found this helpful