Can't use N() with arrays with Excel
<p>I want to compute the weighted mean (WM) of column A (the one on the left) using the weights in column B (right). The problem is that column A can have character values.</p>

<pre><code>    A       B
1   9.1     625
2   8.3     74
3   9.1     655
4   --      20
5   8.8     48
6   10.0    691
7   8.2     107
8   6.4     34
9   7.1     25
10  8.4     126
</code></pre>

<p>I tried to compute the WM with:</p>

<pre><code>=SUMPRODUCT(A1:A10;B1:B10)/SUM(B1:B10)
</code></pre>

<p>but it consider the "--" as zero, leaving the weight unchanged. Instead it should be ignoring both the value and the weight for that row.</p>

<p>So I thought of using array formulas. The first one (F1) coerce to numbers the values so that "--" becomes 0. The second one (F2) force to 0 the weights if the corresponding value is not a number:</p>

<pre><code>F1. {=SUM(N(A1:A10)*B1:B10)} # = 21885.5

F2. {=SUM(IF(N(A1:A10)=0;0;1)*B1:B10)} # = 2405

F3. =F1/F2 # = 9.1, the weighted average
</code></pre>

<ul>
<li>F1 is wrong; it should be 21925.5, but instead looks like just A1 (9.1) is considered in the calculation, not the whole array. If I set A1 to "--" than F1 becomes 0. So it seems that ranges don't play well with N() and instead of an array, just the first transformed value is returned.</li>
<li>F2 is wrong too;  it should be 2385, that is B4 (20) should not be considered in the sum. The problem is the same. In facts if I change the formula to <code>=SUM(IF(A1:A10;1;0)*B1:B10)</code> and change A4 to blank the result is correct.</li>
</ul>

<p>To solve the problem so I need to change "--" to blanks and use this formula for the WM:</p>

<pre><code>{=SUM(A1:A10*IF(A1:A10;1;0)*B1:B10)/SUM(IF(A1:A10;1;0)*B1:B10)}
</code></pre>

<p>My question is: is there a way to use N() with arrays or just a limited number of functions work with arrays and there's nothing I can do?</p>

<p>Thanks</p>