A calculated field based on a null value will usually return a null value. In monarch, Options > View, show nulls as "0.00" or whatever, is purely cosmetic.
If you are opening data from a database, you can replace nulls with default values for the relevant field type ("", 0, 1/1/1970).
When you are taking data from a report, you'll need to use IsNull() in calculated fields to overcome null values, e.g.:
I am using excel as my external database. Some of the columns that i am bringing in do not have data in them yet. However i would still like to bring in these columns so i can set up my model and project for future use. When I create a calculated field to add together the 12 columns the result is zero. I belive it has somehting to do with that some of the columns do not have data yet. How can i fix this. I would like to set up the formuala now to add all of the columns together
The forumala i am using in the calcuated field is just a simple
column a + column b+ columnc+ etc
Your help is greatly appreciated
The easy way to fix this is to create a calculated field for each of the columns you are importing. For each one, the formula should be:
This will replace the null values with zeroes.
Once you've done that, you can hide the original columns from Excel and just show the new calculated fields.
Then to sum them, create a new calculated field as you have already done that adds up the values in the 12 new columns.
You might also consider using a summary to sum your data?