Array Formula Calculates Empty Cells as Zero - incorrect result!

[jswhip]

I am using the percentile function within an array. If I use it in conjunction with with an "IF" statement, I get the wrong result. For example, this formula returns the correct calculation (it's using a much smaller data set than actual).

{=PERCENTILE(L2:L8,0.02)}

However, if I embed this with an "IF" statement/array, I get the wrong result of 14,135:

{=PERCENTILE(IF(A2:A8="FGLMC 3",L2:L8,""),0.02)}

The values in column "L" (used in the Percentile function) are:
<null>
117,793
213,284
188,539
188,763
163,244
162,770

The 2% percentile should be 122,291. If I use the "Evaluate Formula" option from the "Formula" tab, I can see converted the null value to zero.

How do I get Excel to NOT convert the null to zero?

 

[jswhip]

Quick correction - the data values s/b:
<null>
117,793
213,284
188,539
188,763
163,244
162,770

 

[jswhip]

Correction II -

For whatever reason using null within brackets isn't showing. The data values are:
_null_
117,793
213,284
188,539
188,763
163,244
162,770

 

[Kentetsu]

In VBA, when you want to specify a "blank" by using "", you actually have to place your quotes in quotes, like this: """"

So, this is what it should look like in your code: {=PERCENTILE(IF(A2:A8="FGLMC 3",L2:L8,""""),0.02)}

 

[Andrew Poulsom]

Your formula seems to work for me:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	FGLMC 3												122290.7	122290.7
3	FGLMC 3	FGLMC 3										117,793	122290.7	122290.7
4	FGLMC 3	FGLMC 3	FGLMC 3									213,284	162807.9	162807.9
5	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3								188,539	162798.4	162798.4
6	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3							188,763	162789	162789
7	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3						163,244	162779.5	162779.5
8	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3	FGLMC 3					162,770	162770	162770
Sheet8

Cell Formulas
Range	Formula
L2	=""
M2	=PERCENTILE(L2:L$8,0.02)
M3	=PERCENTILE(L3:L$8,0.02)
M4	=PERCENTILE(L4:L$8,0.02)
M5	=PERCENTILE(L5:L$8,0.02)
M6	=PERCENTILE(L6:L$8,0.02)
M7	=PERCENTILE(L7:L$8,0.02)
M8	=PERCENTILE(L8:L$8,0.02)
N2	{=PERCENTILE(IF(A2:A8="FGLMC 3",L2:L8,""),0.02)}
N3	{=PERCENTILE(IF(B2:B8="FGLMC 3",L2:L8,""),0.02)}
N4	{=PERCENTILE(IF(C2:C8="FGLMC 3",L2:L8,""),0.02)}
N5	{=PERCENTILE(IF(D2:D8="FGLMC 3",L2:L8,""),0.02)}
N6	{=PERCENTILE(IF(E2:E8="FGLMC 3",L2:L8,""),0.02)}
N7	{=PERCENTILE(IF(F2:F8="FGLMC 3",L2:L8,""),0.02)}
N8	{=PERCENTILE(IF(G2:G8="FGLMC 3",L2:L8,""),0.02)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jswhip]

Andrew,

Thank you for replying - much appreciated.

Noticed in your example that cell L2 has a formula to return "null". If you were to clear the contents of cell L2, you will see the result I initially described.

For what it's worth, if I enter a space into cell L2, the problem is resolved. It seems using Percentile in conjunction with the array/if formula, Excel evaluates an empty cell as zero. Thoughts on how to resolve?

Thank you.

 

[Andrew Poulsom]

I see. Does this work for you?

=PERCENTILE(IF(A2:A8="FGLMC 3",IF(L2:L8="","",L2:L8)),0.02)

 

[jswhip]

Kentetsu -

Thanks for following up. In this example, the blanks don't come into play as all the rows match the selection criteria. Also, I believe for statistical calculation purposes, a space connotates a much different meaning than missing - missing values aren't used in the calculation whereas spaces would be used. Thus, the wrong result would be returned (at least for my purpose).

 

[jswhip]

That's the trick!

I had tried multiple iterations of your solution, but couldn't get the correct calculation. This saves me the effort of modifying the data set to insure missing values aren't used in statistical calculations.

Thank you very, very much!

 

[Kentetsu]

Yeah, I misread and assumed you were working in vba. Sorry about that.