# Array function with N(Range)?

#### Kelvin Stott

##### Active Member
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

Last edited:

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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

#### Kelvin Stott

##### Active Member
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...

Last edited:

##### MrExcel MVP
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?

Last edited:

#### Kelvin Stott

##### Active Member
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

##### Active Member
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.

Last edited:

##### MrExcel MVP
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

##### Active Member
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))}

##### MrExcel MVP
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

##### Active Member
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?

Last edited:

Replies
7
Views
196
Replies
5
Views
150
Replies
7
Views
356
Replies
5
Views
180
Replies
5
Views
226

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back