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

 

[Aladin Akyurek]

Are you sure about the ranges the formula you posted refers to? One vertical, one horizontal?

 

[Kelvin Stott]

One range (H6:Q6) is a limited horizontal row, while the other (H8:Q12) is an area below that row, so yes, I'm sure that the ranges are correct as defined.

Besides, the first formula works well, except for the fact that it counts text strings as having non-zero value, and that's what I am struggling to fix...

 

[Aladin Akyurek]

One range (H6:Q6) is a limited horizontal row, while the other (H8:Q12) is an area below that row, so yes, I'm sure that the ranges are correct as defined.

Besides, the first formula works well, except for the fact that it counts text strings as having non-zero value, and that's what I am struggling to fix...

Row\Col	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
6​	3​	5​	4​	5​	2​	2​	1​	10​	9​	12​
7​
8​	14​	14​	5​	5​	20​	11​	6​	18​	19​	18​
9​	10​	11​	16​	7​	10​	15​	6​	7​	6​	11​
10​	9​	20​	8​	12​	4​	18​	x​	7​	13​	14​
11​	13​	17​	14​	4​	11​	13​	6​	8​	5​	15​
12​	8​	6​	19​	18​	5​	6​	20​	14​	16​	6​

<tbody>
</tbody>

What is the result for the foregoing sample you wish to obtain?

 

[Kelvin Stott]

The formula finds the lowest value of H6 to Q6 with a non-zero value in the same column below, so in the example table above, the result would be 3 (from H6), regardless of the "x". The problem comes if you leave H8:H12 blank except for an "x".

 

[Kelvin Stott]

Sorry, the value in N6 (=1) is lowest, so the result would and should be 1.

However, if you leave N6 to N12 blank except for the "x" you have, then the result would still be 1 (from N6), but should actually be 2 (from L6 and M6) since the "x" should not be counted as non-zero.

 

[Aladin Akyurek]

Sorry, the value in N6 (=1) is lowest, so the result would and should be 1.

However, if you leave N6 to N12 blank except for the "x" you have, then the result would still be 1 (from N6), but should actually be 2 (from L6 and M6) since the "x" should not be counted as non-zero.

It seems a 0 or empty in a column of H8:Q12 eliminate the corresponding cell in H6:Q6...

Care to test the following...

Control+shift+enter, not just enter:

=MIN(IF(MMULT(TRANSPOSE(ISNUMBER(1/H8:Q12)+0),ROW(H8:Q12)^0)=ROWS(H8:Q12),TRANSPOSE(H6:Q6)))

 

[Kelvin Stott]

Thanks, but I'm afraid your formula didn't work, not sure how or why it was supposed to work...

However you gave me an idea in taking the reciprocal of the range, and this DID work:

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

 

[Aladin Akyurek]

Thanks, but I'm afraid your formula didn't work, not sure how or why it was supposed to work...

However you gave me an idea in taking the reciprocal of the range, and this DID work:

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

Row\Col	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
6​	3​	5​	4​	5​	2​	2​	1​	10​	9​	12​
7​
8​	14​	14​	5​	5​		11​	6​	18​	19​	18​
9​	10​	11​	16​	7​	10​	15​	6​	7​	6​	11​
10​	9​	20​	8​	12​	4​		0​	7​	13​	14​
11​	13​	17​	14​	4​	11​	13​	6​	8​	5​	15​
12​	8​	6​	19​	18​	5​	6​	20​	14​	16​	6​

What is the outcome for the above? Your "inspired" formula says 1.

 

[Kelvin Stott]

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.


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?