Excel Tip of the Week #279 - Revisiting N, T

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a General User post in which we're revisiting some tiny - but useful - little functions: N and T.  These functions were originally covered back in TOTW #104.

What do these functions do?

These functions are very simple functions that include and exclude certain types of data.  N only likes numbers, and ignores other types of data, and T only likes text.

Here's an example of what you might get out of these functions:

Data type Example result Result of N Result of T
Text ABCD 0 ABCD
Number 1234 1234
Date 05/03/2019 43529
Logic value TRUE 1
Logic value FALSE 0

Note that the apparently empty outputs of T are in fact "null text" - so functions like COUNTA will say that the cell is not empty, even though it appears to be.

The syntax for these functions is simple:

=N(data or cell reference)

=T(data or cell reference)

What use are these functions?

These are small, simple functions that can be used for "cleaning" data - provided that you paste values over them afterwords.  A similar result could also be achieved with the Go To Special feature here - see TOTW #102 for more details.

These functions are also useful for making sure that an input to a formula is of the expected type - because inappropriate types are converted to 0 / null text, inappropriate data of all kinds is condensed into only one.  So for example you could detect easily if an input was appropriate for a numerical calculation:

=N(input)<>0

There's also a niche use of N for adding in-line comments to formulas:

Here the comment is embedded into the formula, but the N function means that the text has no actual effect on the result calculated.

Previous post - Using data tables for Monte Carlo simulations
TOTW index 
Next post - SUM and COUNT functions redux

Anonymous