Array function with N(Range)?

[Kelvin Stott]

I have the following array formula which finds the minimum reference year (H6:Q6) which corresponds to any of the cells below (in H8:Q12) having a non-zero value:

={MIN(IF(H8:Q12<>0,H6:Q6,9999))}

However, the formula above mistakes a text string (e.g., "a") as a non-zero value, so I tried to modify as follows:

={MIN(IF(N(H8:Q12)<>0,H6:Q6,9999))}

However, the modified formula above doesn't work properly as an array formula.

Also, this simple function below does not work properly as an array function:

{=SUM(N(G23:G27))}

Any ideas why, and how I can fix to consider text strings as zero value within an array function?

Thanks,
Kelvin

 

[Kelvin Stott]

Mind you, I still don't understand why {=SUM(N(G23:G27))} doesn't work as a proper array function, by summing up all numerical values in G23:G27.

Perhaps N() just can't work as an array function? Any thoughts?

... but strangely this equivalent array formula does work:

{=SUM(N(IF(G23:G27=G23:G27,G23:G27,0)))}

So why do these two formulae behave so differently?

 

[Aladin Akyurek]

Yes, it should be 1, because there are non-zero values under N6, which has the lowest value (1) from H6 to Q6.

Note that if you leave N8:N12 blank, the result with the new formula would be 2 (from L6 and M6), even if you leave an "x" anywhere in N8:N12. That's how it should be, but not what my original formula gave.

So all is fine now.

Control+shift+enter:

=MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6))

will suffice and is faster.

Note. The MMULT formula I proposed solves a slightly different problem than one eventually cleared up.

Mind you, I still don't understand why {=SUM(N(G23:G27))} doesn't work as a proper array function, by summing up all numerical values in G23:G27.

Perhaps N() just can't work as an array function? Any thoughts?

Hmm, strange. I does so in/when...

Row\Col	G​	H​	I​
23​	3​	x	7​
24​	4​	x
25​

I23:

=SUMPRODUCT(N(H23:H24="x"),G23:G24)

>>

SUMPRODUCT({1;1},{3;4})

>> 7

 

[Kelvin Stott]

Control+shift+enter:

=MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6))

will suffice and is faster.

Unfortunately that doesn't work if all cells in H8:Q12 have zero value, so need the 9999 as default minimum year to account for that:

{=MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6,9999))}

 

[Aladin Akyurek]

Unfortunately that doesn't work if all cells in H8:Q12 have zero value, so need the 9999 as default minimum year to account for that:

{=MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6,9999))}

In Excel defaulting to a positive or a negative 9.99999999999999E+307 would make more sense!...

 

[Kelvin Stott]

Agreed, except in this case I convert the year into date format with DATE(year, month, day), so need to limit the year to 9999 otherwise I get an error.

FYI, the overall array function is designed to give the earliest year/date when costs are given below that year, to determine when a project starts, so I just need some some maximum default value (year) to indicate "never".

 

[Aladin Akyurek]

Agreed, except in this case I convert the year into date format with DATE(year, month, day), so need to limit the year to 9999 otherwise I get an error.

FYI, the overall array function is designed to give the earliest year/date when costs are specified below that year.

How about?

{=IF(SUM(ISNUMBER(1/H8:Q12)+0),DATE(MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6)),1,1),"")}

 

[Kelvin Stott]

Yes, that also works, but not as short and simple as:

{=DATE(MIN(IF(ISNUMBER(1/H8:Q12),H6:Q6,9999)),1,1)}