The Magical N Function

The N function is generally used to convert text to zero. This is useful if a cell that should have a number has text instead. The N function copes with this easily - see images below.

Without the N function.

With the N function.

The N function has another use – handling logic results correctly.

In Excel TRUE = 1 and FALSE = 0.

In the image below you can see this when you multiply a logical test (TRUE or FALSE) result by 1.

Instead of multiplying by 1 you can also use the N function. See image below.

How cool is that?!

This has the added advantage that if text is entered in a cell that should have TRUE or FALSE, then it will be treated as zero. When multiplying by 1 any text causes the #VALUE! error - see below.

With the N function text is treated as zero or FALSE.

This may occur if you are using a checkbox to enter TRUE or FALSE in a linked cell. The linked cell is usually unlocked, so anyone can enter anything in it.  Using N avoids the #VALUE! error for calculations as it treats text entries as zero.