#### Kelvin Stott

##### Active Member

- Joined
- Oct 26, 2010

- Messages
- 338

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

={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: