N function

PRO BASIC

The N function in Jspreadsheet Formulas Pro is a tool that converts a given value into a numerical format. This is particularly useful when you're dealing with data that may not initially appear as a number. For example, the function can turn a date into its equivalent numerical value, or a true/false statement into 1 or 0 respectively. Essentially, the N function helps you to standardize and work with your data in a numeric context.

Documentation

Returns a value converted to a number.

Syntax

N(value)

Parameter	Description
`value`	The value you want to convert to a number. If value is a cell reference, its contents are used.

The 'N' function is used to convert a value to a number. Here is the requested information:

Behavior

The 'N' function behaves differently depending on the data type passed to it:

Numbers: If the cell contains a number, the function will return the same number.
Text: If the cell contains text, the function will return 0.
Booleans: If the cell contains TRUE, the function will return 1. If the cell contains FALSE, the function will return 0.
Errors: If the cell contains an error value, the function will return the error.
Empty cells: If the cell is empty, the function will return 0.

Common Errors

Error Name	Description
#VALUE!	This error occurs when the function receives an argument that it does not know how to handle.

Best practices

Always ensure that the data type of the value you are passing to the 'N' function is one that it can handle, i.e., a number, text, Boolean, or error.

Use the 'N' function to convert Boolean values to numerical values for use in mathematical operations.

Be aware that the 'N' function will convert text to 0, so ensure this behavior is expected in your calculations.

If using the 'N' function in a large range of cells, be sure to handle or filter out error values to avoid unexpected results.

Usage

A few examples using the N function.

N(42) → 42  
N("100") → 100  
N(TRUE) → 1  
N(FALSE) → 0  
N("Text") → 0

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OGFjYzBkMWZjN2I2NWYyMjc4NTMyMzNlMjE4Zjc4OTlkODQ0MjgxOGI0NWUyYjFhMjZjZDg4MjRhNWI4ODJiYTlhNThiNDcwZjNjNzBiMjc1YTcyOGEzNTMzNDkyODk3ZTEzZGM0MDljMDgwYmQ1MDcxZmRmMWQxN2Q0MmM3ZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Raw Data",
        "Converted to Number"
    ],
    [
        "42",
        "=N(A2)"
    ],
    [
        "-$150.75",
        "=N(A3)"
    ],
    [
        "1,250",
        "=N(A4)"
    ],
    [
        true,
        "=N(A5)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('OGFjYzBkMWZjN2I2NWYyMjc4NTMyMzNlMjE4Zjc4OTlkODQ0MjgxOGI0NWUyYjFhMjZjZDg4MjRhNWI4ODJiYTlhNThiNDcwZjNjNzBiMjc1YTcyOGEzNTMzNDkyODk3ZTEzZGM0MDljMDgwYmQ1MDcxZmRmMWQxN2Q0MmM3ZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Raw Data",
        "Converted to Number"
    ],
    [
        "42",
        "=N(A2)"
    ],
    [
        "-$150.75",
        "=N(A3)"
    ],
    [
        "1,250",
        "=N(A4)"
    ],
    [
        true,
        "=N(A5)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('OGFjYzBkMWZjN2I2NWYyMjc4NTMyMzNlMjE4Zjc4OTlkODQ0MjgxOGI0NWUyYjFhMjZjZDg4MjRhNWI4ODJiYTlhNThiNDcwZjNjNzBiMjc1YTcyOGEzNTMzNDkyODk3ZTEzZGM0MDljMDgwYmQ1MDcxZmRmMWQxN2Q0MmM3ZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Raw Data",
        "Converted to Number"
    ],
    [
        "42",
        "=N(A2)"
    ],
    [
        "-$150.75",
        "=N(A3)"
    ],
    [
        "1,250",
        "=N(A4)"
    ],
    [
        true,
        "=N(A5)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OGFjYzBkMWZjN2I2NWYyMjc4NTMyMzNlMjE4Zjc4OTlkODQ0MjgxOGI0NWUyYjFhMjZjZDg4MjRhNWI4ODJiYTlhNThiNDcwZjNjNzBiMjc1YTcyOGEzNTMzNDkyODk3ZTEzZGM0MDljMDgwYmQ1MDcxZmRmMWQxN2Q0MmM3ZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Raw Data",
        "Converted to Number"
    ],
    [
        "42",
        "=N(A2)"
    ],
    [
        "-$150.75",
        "=N(A3)"
    ],
    [
        "1,250",
        "=N(A4)"
    ],
    [
        true,
        "=N(A5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub