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: