30 Excel Functions in 30 Days: 22 – N

Icon30DayYesterday, in the 30XL30D challenge, we identified cell contents with the TYPE function, and used it to check for a number, before multiplying.

For day 22 in the challenge, we’ll examine the N function. It’s similar to the T function that we saw earlier, but checks for numbers instead of text.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the N information and examples, and if you have other tips or examples, please share them in the comments.

Function 22: N

The N function returns a value converted to a number.

N00

How Could You Use N?

The N function has limited use, and can usually be replaced by a different function or operator. You can:

  • Return number based on a cell’s value
  • Use N function alternatives
  • Add a hidden note in a cell

N Syntax

The N function has the following syntax:

  • N(value)
    • value can be text, number, or any other value

N Traps

If the value is an error, the N function returns the same error, instead of a number. Use IFERROR or ISNUMBER to handle the errors.

Example 1: Return a Number Based on Cell Value

With the N function, you can return a number, based on a cell’s value. If the value is a number, then that number is returned.

=N(C3)

N01a

This table shows the result of other values in the N function.

N01b

Example 2: N Function Alternatives

Instead of the N function, you can use ISNUMBER to test for numbers.

=ISNUMBER(C3)

N02a

Or use two minus signs (double unary) to convert TRUE/FALSE or text numbers to numbers.

=IFERROR(–C3,””)

N02b

Example 3: Add Hidden Note to Cell

If you want to add a hidden note in a cell, you can use the N function. Because the result of a text value in the N function is zero, adding this note won’t affect the cell result.

The note will only be visible in the formula bar, when the cell is selected.

=SUM(B3:C3)  + N(“Store01 closed in June”)

N03

Download the N Function File

To see the formulas used in today’s examples, you can download the N function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the N Video

To see a demonstration of the examples in the N function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Return Cell Value as Number with Excel N Function

_____________

You may also like...

6 Responses

  1. BDT says:

    I love the “hidden comment” approach! I hadn’t seen that before.

    Do you have any more thoughts on using N() vs using — (double unary)? Is it better to use one versus the other? Are there compatibility issues with other programs or prev versions of Excel?

  2. Luke Wisbey says:

    N and double unary do separate things – i.e. the former does not explicitly coerce.

    Say you have 2 cells (A1,B1) and you wish to subtract B1 from A1 but B1 may or may not contain a number

    =A1-B1

    would be susceptible to #VALUE! error

    =A1-N(B1)

    would not – nor for that matter would A1-SUM(B1)

  3. Luke Wisbey says:

    edit: to clarify the above (for the pedants) – we are assuming that A1 is always numeric and that neither A1 nor B1 would ever contain underlying error values…

  4. Dave says:

    If you like the hidden note using =n(), then you may like the hidden note using text:

    =”Due date: ” & text(a1,”mmmm dd, yyyy”)
    & text(“they never pay on time!”,”;;;”)

    Or should this be held until the =text() function????

  5. Thanks BDT, and thanks Luke, for answering the question.

    Dave, the poor TEXT function didn’t get enough votes to be included, so thanks for your example!

  6. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] N […]

Leave a Reply

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