Is there a Null function?

[JenniferMurphy]

This page says that there is a Null function in Excel:

NULL function - Microsoft Support

But if I enter "=null()" in a cell, I get a #NAME? error.

I ask because I want a cell formula to return Null if one of the source cells is missing. The tables below illustrate what I am trying to do. The mini-sheet follows.

Table 1 has 2 data columns with all values present. The two Z columns calculate the Z Scores for the data columns. The Wt ZSum column calculates the weighted sum of the Z Scores.

Table2 shows what happens if some data values are missing. The data means & std devs are correct, but the Z Scores are off.

In Table3, I set the Z Score to zero for any missing data values. Now the Z Score means are correct, but the std devs are off.

In Table 4, I set the Z Score to null (""?) for any missing data values. The Z Score means & std devs are correct, but I get a value error in the sum. I tried returning null(), but that got a #NAME? error as stated above.

In Table 5, I manually set the Z Score to null (delete key) for any missing data values. Now everything is correct. How can I do this in a formula?

Weighted Ratings Demo.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
2	Table1							Table2							Table3							Table4							Table5
3	Weights	2	1					Weights	2	1					Weights	2	1					Weights	2	1					Weights	2	1
4		A	B	Z A	Z B	Wt ZSum			A	B	Z A	Z B	Wt ZSum			A	B	Z A	Z B	Wt ZSum			A	B	Z A	Z B	Wt ZSum			A	B	Z A	Z B	Wt ZSum
5		1	9	-1.26	+1.26	-1.26			1	9	-1.32	+1.02	-1.61			1	9	-1.32	+1.02	-1.61			1	9	-1.32	+1.02	-1.61			1	9	-1.32	+1.02	-1.61
6		2	8	-0.63	+0.63	-0.63				8	-1.90	+0.44	-3.37				8	=0.00	+0.44	+0.44				8		+0.44	#VALUE!				8		+0.44	+0.44
7		3	7	=0.00	=0.00	=0.00			3	7	-0.15	-0.15	-0.44			3	7	-0.15	-0.15	-0.44			3	7	-0.15	-0.15	-0.44			3	7	-0.15	-0.15	-0.44
8		4	6	+0.63	-0.63	+0.63			4		+0.44	-4.25	-3.37			4		+0.44	=0.00	+0.88			4		+0.44		#VALUE!			4		+0.44		+0.88
9		5	5	+1.26	-1.26	+1.26			5	5	+1.02	-1.32	+0.73			5	5	+1.02	-1.32	+0.73			5	5	+1.02	-1.32	+0.73			5	5	+1.02	-1.32	+0.73
10	Means	3.00	7.00	0.00	0.00	0.00		Means	3.25	7.25	-0.38	-0.85	-1.61		Means	3.25	7.25	0.00	0.00	0.00		Means	3.25	7.25	0.00	0.00	#VALUE!		Means	3.25	7.25	0.00	0.00	0.00
11	Std Devs	1.58	1.58	1.00	1.00	1.00		Std Devs	1.71	1.71	1.21	2.09	1.80		Std Devs	1.71	1.71	0.87	0.87	1.04		Std Devs	1.71	1.71	1.00	1.00	#VALUE!		Std Devs	1.71	1.71	1.00	1.00	1.04
Sheet5

Cell Formulas
Range	Formula
J3,AE3,X3,Q3	J3	=AWt
K3,AF3,Y3,R3	K3	=BWt
E5:E9	E5	=([@A]-Table1[[#Totals],[A]])/C$11
F5:F9	F5	=([@B]-Table1[[#Totals],[B]])/D$11
G5:G9,AI5:AI9,AB5:AB9,U5:U9,N5:N9	G5	=[@[Z A]]*AWt + [@[Z B]]*BWt
L5:L9	L5	=([@A]-Table2[[#Totals],[A]])/J$11
M5:M9	M5	=([@B]-Table2[[#Totals],[B]])/K$11
S5:S9	S5	=IF(ISNUMBER([@A]), ([@A]-Table3[[#Totals],[A]])/Q$11, 0)
T5:T9	T5	=IF(ISNUMBER([@B]), ([@B]-Table3[[#Totals],[B]])/R$11, 0)
Z5:Z9	Z5	=IF(ISNUMBER([@A]), ([@A]-Table4[[#Totals],[A]])/X$11, "")
AA5:AA9	AA5	=IF(ISNUMBER([@B]), ([@B]-Table4[[#Totals],[B]])/Y$11, "")
AG5,AG7:AG9	AG5	=IF(ISNUMBER([@A]), ([@A]-Table5[[#Totals],[A]])/AE$11, "")
AH5:AH7,AH9	AH5	=IF(ISNUMBER([@B]), ([@B]-Table5[[#Totals],[B]])/AF$11, "")
C10,AE10,X10,Q10,J10	C10	=SUBTOTAL(101,[A])
D10,AF10,Y10,R10,K10	D10	=SUBTOTAL(101,[B])
E10,AG10,Z10,S10,L10	E10	=SUBTOTAL(101,[Z A])
F10,AH10,AA10,T10,M10	F10	=SUBTOTAL(101,[Z B])
G10,AI10,AB10,U10,N10	G10	=SUBTOTAL(101,[Wt ZSum])
C11	C11	=STDEV.S(Table1[A])
D11	D11	=STDEV.S(Table1[B])
E11	E11	=STDEV.S(Table1[Z A])
F11	F11	=STDEV.S(Table1[Z B])
G11	G11	=STDEV.S(Table1[Wt ZSum])
J11	J11	=STDEV.S(Table2[A])
K11	K11	=STDEV.S(Table2[B])
L11	L11	=STDEV.S(Table2[Z A])
M11	M11	=STDEV.S(Table2[Z B])
N11	N11	=STDEV.S(Table2[Wt ZSum])
Q11	Q11	=STDEV.S(Table3[A])
R11	R11	=STDEV.S(Table3[B])
S11	S11	=STDEV.S(Table3[Z A])
T11	T11	=STDEV.S(Table3[Z B])
U11	U11	=STDEV.S(Table3[Wt ZSum])
X11	X11	=STDEV.S(Table4[A])
Y11	Y11	=STDEV.S(Table4[B])
Z11	Z11	=STDEV.S(Table4[Z A])
AA11	AA11	=STDEV.S(Table4[Z B])
AB11	AB11	=STDEV.S(Table4[Wt ZSum])
AE11	AE11	=STDEV.S(Table5[A])
AF11	AF11	=STDEV.S(Table5[B])
AG11	AG11	=STDEV.S(Table5[Z A])
AH11	AH11	=STDEV.S(Table5[Z B])
AI11	AI11	=STDEV.S(Table5[Wt ZSum])

Named Ranges
Name	Refers To	Cells
AWt	=Sheet5!$C$3	AE3, X3, Q3, J3, G5:G9, N5:N9, U5:U9, AB5:AB9, AI5:AI9
BWt	=Sheet5!$D$3	AF3, Y3, R3, K3, G5:G9, N5:N9, U5:U9, AB5:AB9, AI5:AI9

 

[MARK858]

The function NULL is for Sharepoint in a calculated column not Excel

 

[JenniferMurphy]

The function NULL is for Sharepoint in a calculated column not Excel

Aha! I didn't pay attention to that.

So, is there any way for the formulas in Column S, which return a zero, or Column Z, which return "", to return a value that will be treated as an empty cell?

 

[Alex Blakenburg]

Using the Table4 version of returning "", is this an option for the sum column ?

=IF([@[Z A]]<>"",[@[Z A]])*AWt + IF([@[Z B]]<>"",[@[Z B]])*BWt

 

[JenniferMurphy]

Using the Table4 version of returning "", is this an option for the sum column ?

=IF([@[Z A]]<>"",[@[Z A]])*AWt + IF([@[Z B]]<>"",[@[Z B]])*BWt

Yes, that works, but what a pain! I am going to move all of this to a macro where I can use tests that I have a little more confidence in.

I did a little more testing. I'll post screenshots first and then put the mini-sheets at the end.

Test1: I added a Table6 where I defined a named cell (Null) that was empty (AO2). I then returned that in the formula.

=IF(ISNUMBER([@A]),  ([@A]-Table6[[#Totals],[A]])/AL$11,  Null)

It didn't work. The cells that I need to be empty (AN6 & AO8) still show up as zero.

Test2: In one cell (C4) I put ="". Another (D4) was empty. I then copied each to another cell (E4, F4) and ran as many ISxx functions as I could find on all 4 cells. The differences are highlighted.

1. The yellow cells (C6:D6) show that the empty cell (D4) is blank, but the ="" cell (C4) is not.
2. The pink cells (C9:D9) show that the ="" cell (C4) contains text, the empty cell (D4) does not.
3. The orange cells (B5, F5) show that the empty cell (D4) is not a number, but its copy (F4) is.
4. The green cell (F6) and the second yellow cell (D6) show that while the empty cell (B4) is blank, its copy (F4) is not.
5. The blue cells (D10, F10) show that the empty cell (D4) is equal to "", but its copy (F4) is not.

Finally, the computational cells (G4:J4) show that an empty cell is treated as a zero in formulas, but a ="" cell is treated as text.

I think Excel needs a Null() function.

Here are the mini-sheets:

Weighted Ratings Demo.xlsx
	AK	AL	AM	AN	AO	AP
2	Table6			Null
3	Weights	2	1
4		A	B	Z A	Z B	Wt ZSum
5		1	9	-1.32	+1.02	-1.61
6			8	=0.00	+0.44	+0.44
7		3	7	-0.15	-0.15	-0.44
8		4		+0.44	=0.00	+0.88
9		5	5	+1.02	-1.32	+0.73
10	Means	3.25	7.25	0.00	0.00	0.00
11	Std Devs	1.71	1.71	0.87	0.87	1.04
Null Test1

Cell Formulas
Range	Formula
AL3	AL3	=AWt
AM3	AM3	=BWt
AN5:AN9	AN5	=IF(ISNUMBER([@A]), ([@A]-Table6[[#Totals],[A]])/AL$11, Null)
AO5:AO9	AO5	=IF(ISNUMBER([@B]), ([@B]-Table6[[#Totals],[B]])/AM$11, Null)
AP5:AP9	AP5	=[@[Z A]]*AWt + [@[Z B]]*BWt
AL10	AL10	=SUBTOTAL(101,[A])
AM10	AM10	=SUBTOTAL(101,[B])
AN10	AN10	=SUBTOTAL(101,[Z A])
AO10	AO10	=SUBTOTAL(101,[Z B])
AP10	AP10	=SUBTOTAL(101,[Wt ZSum])
AL11	AL11	=STDEV.S(Table6[A])
AM11	AM11	=STDEV.S(Table6[B])
AN11	AN11	=STDEV.S(Table6[Z A])
AO11	AO11	=STDEV.S(Table6[Z B])
AP11	AP11	=STDEV.S(Table6[Wt ZSum])

Named Ranges
Name	Refers To	Cells
'Null Test1'!AWt	='Null Test1'!$C$3	AL3, AP5:AP9
'Null Test1'!BWt	='Null Test1'!$D$3	AM3, AP5:AP9
'Null Test1'!Null	='Null Test1'!$AO$2	AN5:AO9

Weighted Ratings Demo.xlsx
	B	C	D	E	F	G	H	I	J
3		A (="")	B (empty)	AA (=A)	BB (=B)	A*5	B*5	AA*5	BB*5
4					0	#VALUE!	0	#VALUE!	0
5	=ISNUMBER	FALSE	FALSE	FALSE	TRUE
6	=ISBLANK	FALSE	TRUE	FALSE	FALSE
7	=ISNA	FALSE	FALSE	FALSE	FALSE
8	=ISOMITTED	FALSE	FALSE	FALSE	FALSE
9	=ISTEXT	TRUE	FALSE	TRUE	FALSE
10	=""	TRUE	TRUE	TRUE	FALSE
Null Test2

Cell Formulas
Range	Formula
C4	C4	=""
E4	E4	=A
F4	F4	=B
G4	G4	=A*5
H4	H4	=B*5
I4	I4	=AA*5
J4	J4	=BB*5
C5	C5	=ISNUMBER(A)
D5	D5	=ISNUMBER(B)
E5	E5	=ISNUMBER(AA)
F5	F5	=ISNUMBER(BB)
C6	C6	=ISBLANK(A)
D6	D6	=ISBLANK(B)
E6	E6	=ISBLANK(AA)
F6	F6	=ISBLANK(BB)
C7	C7	=ISNA(A)
D7	D7	=ISNA(B)
E7	E7	=ISNA(AA)
F7	F7	=ISNA(BB)
C8	C8	=ISOMITTED(A)
D8	D8	=ISOMITTED(B)
E8	E8	=ISOMITTED(AA)
F8	F8	=ISOMITTED(BB)
C9	C9	=ISTEXT(A)
D9	D9	=ISTEXT(B)
E9	E9	=ISTEXT(AA)
F9	F9	=ISTEXT(BB)
C10	C10	=A=""
D10	D10	=B=""
E10	E10	=AA=""
F10	F10	=BB=""

Named Ranges
Name	Refers To	Cells
'Null Test2'!A	='Null Test2'!$C$4	G4, E4, C5:C10
'Null Test2'!AA	='Null Test2'!$E$4	I4, E5:E10
'Null Test2'!B	='Null Test2'!$D$4	H4, F4, D5:D10
'Null Test2'!BB	='Null Test2'!$F$4	J4, F5:F10

 

[Alex Blakenburg]

And as you have discovered different functions treat them differently. Some see blank and "" as being the same and some do not eg
=COUNTIFS($D$11:$D$15,"<>" & "") ONLY excludes "empty" cells (generally abbreviated to just "<>")
While SumProduct, Filter etc using the syntax ($D$11:$D$15 <> "") excludes BOTH empty cells and ""
In VBA IsEmpty only picks up empty cells while = "" picks up BOTH empty cells and ""

 

[JenniferMurphy]

And as you have discovered different functions treat them differently. Some see blank and "" as being the same and some do not eg
=COUNTIFS($D$11:$D$15,"<>" & "") ONLY excludes "empty" cells (generally abbreviated to just "<>")
While SumProduct, Filter etc using the syntax ($D$11:$D$15 <> "") excludes BOTH empty cells and ""
In VBA IsEmpty only picks up empty cells while = "" picks up BOTH empty cells and ""

Yep. Just another of the many joys of working with products in Office. It has a hard and fast rule that before you can do pretty much anything, you have to do something else first. And that rule is infinitely recursive.

At least with a VBA macro, I have much better testing capabilities. And if I find a bug/problem, fixing it fixes all of the calls.