## Fix #N/A Errors in Simple Formulae.

Commonly in Excel we have a matrix of values, and want to add
up the contents of a full row, or column. The simplest way is to move to the
bottom of that column, and click the SUM button ()
which will set up the SUM function to add up the numeric values above it - e.g.
SUM(B2:B500).
If the matrix of values comes from other calculations, such
as VLOOKUPs, we sometimes find that
one value is missing, and has #N/A in the cell. This error propagates to the SUM
function, causing that to display #N/A too. This can be useful in that it may
alert us that a value hasn't been found, and we can then fix the problem
(possibly using the VLOOKUP assistant).
It can also be a complete pain when we know that value is missing, and we just
want the total of the rest.

The Fix #N/A macro will ignore any cells that Excel flags as
an error, treating them as blanks. An example of such an error is shown below ;

Clicking yes would change the new formula into the cell,
fixing the problem. Note that the double quotes shown in the middle of the new
formula means treat the erroneous cells as blanks. We could change this to zero,
but bear in mind that this may have an implications if we were using a function
like AVERAGE. The AVERAGE function would ignore blanks completely, making the
average of the numbers shown in the example above (5000+2000+7000)/3, whereas
using zero would calculate the average as (5000+2000+0+7000)__/4__. Dividing
the result by 4 and not 3.

If you do alter the formula again, you'll need to press
Control, Shift, and Enter rather than just error to preserve the new
functionality. That is because the new formula is an array style formula (see
below for details).

### So how does it work ?

The formula has been modified and changed to an array style
formula. In these kinds of formulae, each value in a range is evaluated in the
condition separately, the result going into the surrounding formula. So in the
example above, the IF function is evaluated against ISERROR(C3) first, returning
C3 in this case, because C3 is not in an error state. The same test is applied
against each cell in the range C3:C6, then the values return are substituted
into the SUM function as SUM(5000,2000,"",7000).

There are many more examples of array functions on the web.