The Magical N Function in Excel

Who you calling short?

One reason I like the N function is because it is Excel’s shortest function name. But it has quite a few useful features as well.

I have written about it previously at this blog post where I show how it converts text to zero.

I have found another use – handling logic results.

In Excel TRUE = 1 and FALSE = 0.

In the image below you can see this when you multiply a logical test 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. See comparison below.

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.