Array formula (stdev if function) -> how to ignore blanks?

[paupaj]

Hello,

Can anyone help me find a way for this function to ignore blank cells?

Yellow cell = Result without blank row {=STDEV.S(IF(A1:A3=A1,C1:C3),IF(B1:B3=B1,(D1:D3)*-1))}
Green cell = Result with blank row {=STDEV.S(IF(A1:A4=A1,C1:C4),IF(B1:B4=B1,(D1:D4)*-1))}

The goal: green cell result = yellow cell result

Would really appreciate any help!
<3

 

[KRice]

The issue seems to be that a blank in columns C and D is treated as a 0 if the logical test results in a blank cell being included. I'm assuming you want to prevent these blank cells from being incorporated into the arrays as 0's, so I've added another logical expression to the part of the formula that forms the array arguments of STDEV.S IFERROR functions trap any division by 0 errors, replacing them with blanks. Results are shown in the orange and blue cells.

MrExcel20200915.xlsx
	A	B	C	D
1	Apple	Banana	-7	7
2	Apple	Cherry	46	-46
3	Date	Banana	43	-43
4	Apple	Date
5
6	29.75875	29.75875
7	27.10166	29.75875
paupaj

Cell Formulas
Range	Formula
A6	A6	=STDEV.S(IF(A1:A3=A1,C1:C3),IF(B1:B3=B1,(D1:D3)*-1))
B6	B6	=STDEV.S(IFERROR((C1:C3)/(A1:A3=A1)/(C1:C3<>""),""),IFERROR(-(D1:D3)/(B1:B3=B1)/(D1:D3<>""),""))
A7	A7	=STDEV.S(IF(A1:A4=A1,C1:C4),IF(B1:B4=B1,(D1:D4)*-1))
B7	B7	=STDEV.S(IFERROR((C1:C4)/(A1:A4=A1)/(C1:C4<>""),""),IFERROR(-(D1:D4)/(B1:B4=B1)/(D1:D4<>""),""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.