The function N in Google Sheets is not so popular. I was also not sure about the usefulness of this function. But quite recently I’ve found that this function is quite useful to sort out some formula problems.
In this Google Sheets tutorial, you can learn the use of Google Sheets N function. Let me introduce you the syntax, some basic examples and then the real use of this function.
How to Use the Function N in Google Sheets
The function N returns the provided argument as a number. Let me start with the syntax first.
The N Function Syntax
N(value)
If you feed any value to N, no matter whether it’s a number, text, Boolean True/False or even a tick box, the function converts it to a number. See the below few examples to understand this.
Basic Examples of Google Sheets N function
The below example shows how the N function responds to different arguments.
Let me start with the formula in Cell E2. The N function converts the date entry in Cell D2 to the equivalent date value. If you check the other formulas you can see that the numbers are returned as same numbers, the text is converted as numeric value 1, the Boolean True is 1 and the False is 0. The Boolean part is applicable to the Tick Boxes also.
The Practical Use of Google Sheets N Function [N Function in Complex Formulas]
The function N in Google Sheets can also take an array as the reference. For example, I can enter an Array formula in cell E2 (please refer the above screenshot) instead of entering several non-array formulas in the range E2: E10.
=ArrayFormula(N(D2:D10))
This feature we can intelligently use in formulas using MMULT where the blank cells in any of the two arrays cause an error.
In such cases, we can first fill all the blank cells with the value 0 by using the function N in Google Sheets. Here is that example.
How to Fill Blank Cells With the Value Zero in Google Sheets?
See the values in the range in C1: E4Â where the Cells D2 and E3 are blanks. The N formula in G1 fill those blank cells with the value 0.
If you are an advanced Google Sheets user, here is one example that shows how to use the function N in MMULT in Google Sheets.
Google Sheets N Function in MMULT to Avoid #VALUE! Error
See one normal MMULT formula example.
In this example, I’ve entered one formula in the Cell G8 which is as follows.
=mmult(transpose(F3:F6),G3:I6)
It multiplies the rate column (F) with Column G, Column H and Column I. I’m not going into that details.
Now just delete any of the values in the range G3: I6. No surprise, the MMULT formula would return an error. To avoid that you can wrap the range G3: I6 with the N formula as below.
=ArrayFormula(mmult(transpose(F3:F6),N(G3:I6)))
When using the function N in a range, do apply ArrayFormula. Hope this makes clear.
