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

# adding column that have null values

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

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]) ENDEND`

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

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

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