I was recently reminded of how handy it can be to create a “NULL” value in a formula. I had to use this in two recent situations and thought a description of these two uses would make a great blog post. But, as happens so often, I looked at my past posts and found I had written about both of these before, one in 2016 and one in 2007. So rather than write them up again I am just going to link to those posts with some brief comments.

1) If you ever need to do a distinct count of values that meet a condition. This usually means an IF THEN ELSE formula and if you don’t use a NULL as your ‘ELSE’ your distinct count is likely to be inflated by one.

2) If you want to do a minimum or maximum of values that met a condition. Again you would need an IF THEN ELSE formula and the simplest approach I have found is to use a NULL as your ‘ELSE’ value.

Note 1: Don’t think that skipping the ELSE altogether will generate a NULL value.  It will generate a default value (empty string, zero, etc.).

Note 2:  These NULL formulas are strings, so if you need an else that is a NULL numeric you can use:

Val({@Null})

If your else value needs to be a date you can use:

Date (Val({@Null}),1,1 )

These will still be null values, but they will have the correct data type for your formula.