DocumentationFormulasFunctionsLognormdist

LOGNORMDIST function

PRO

The LOGNORMDIST function in Jspreadsheet Formulas Pro returns the cumulative log-normal distribution of a positive value x, where the natural logarithm of x is normally distributed with the given mean and standard deviation. The log-normal distribution is used for quantities that cannot be negative and are skewed to the right, such as prices or durations. It is the compatibility version of LOGNORM.DIST and always returns the cumulative probability.

Documentation

Returns the cumulative log-normal distribution for a specified value, mean and standard deviation.

Category

Statistical

Syntax

LOGNORMDIST(x, mean, standard_dev)

Parameter Description
x The value at which to evaluate the function. Must be greater than 0.
mean The mean of LN(x).
standard_dev The standard deviation of LN(x). Must be greater than 0.

Behavior

The LOGNORMDIST function returns NORM.S.DIST((LN(x) - mean) / standard_dev, TRUE), the probability that a log-normal variable is less than or equal to x.

  • There is no cumulative argument. Use LOGNORM.DIST for the density form.
  • x must be strictly positive and standard_dev must be positive. Otherwise the function returns #NUM!.
  • mean can be any number, including 0 and negative values.
  • For mean 0 and standard_dev 1, LOGNORMDIST(1, 0, 1) returns 0.5 because LN(1) is 0.
  • Text arguments return #VALUE!.

Common Errors

Error Description
#NUM! Returned when x is less than or equal to 0 or when standard_dev is less than or equal to 0.
#VALUE! Returned when an argument is text that cannot be interpreted as a number.

Best practices

  • Validate the input cells so that x and standard_dev are greater than 0, or wrap the call with IFERROR when x comes from data that may contain zeros.
  • Use absolute references for the mean and standard_dev cells when copying the formula down a column of x values, for example $B$1.
  • Check with ISNUMBER that the input cells hold numbers, because text returns #VALUE!.

Usage

A few examples using the LOGNORMDIST function.

LOGNORMDIST(1, 0, 1)
// Returns 0.5

LOGNORMDIST(4, 3.5, 1.2)
// Returns 0.039084

LOGNORMDIST(0, 0, 1)
// Returns #NUM! because x must be positive

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.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>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MjJhMzFhMGZjYmUzYzY5MTQwMGNhOTY1MWMyODExMzVlNDVjYzJhOTcyNjZhNGU1MzgyYjZjZGU5MDhiZWUyOGIyOGUyOGYzZjI5MzlhMTU4NDg1ZGFmYTA1Y2Q5OGY4MDcyZjlkZjhiNDgzYjI1ZDQ5ZTliNDE0NmVlMDZjYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9UYzRNekU1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "x",
        "mean",
        "standard_dev",
        "LOGNORMDIST"
    ],
    [
        1,
        0,
        1,
        "=LOGNORMDIST(A2, B2, C2)"
    ],
    [
        2,
        0,
        1,
        "=LOGNORMDIST(A3, B3, C3)"
    ],
    [
        4,
        3.5,
        1.2,
        "=LOGNORMDIST(A4, B4, C4)"
    ],
    [
        50,
        3.5,
        1.2,
        "=LOGNORMDIST(A5, B5, C5)"
    ]
]
  }]
});
</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('MjJhMzFhMGZjYmUzYzY5MTQwMGNhOTY1MWMyODExMzVlNDVjYzJhOTcyNjZhNGU1MzgyYjZjZGU5MDhiZWUyOGIyOGUyOGYzZjI5MzlhMTU4NDg1ZGFmYTA1Y2Q5OGY4MDcyZjlkZjhiNDgzYjI1ZDQ5ZTliNDE0NmVlMDZjYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9UYzRNekU1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "x",
        "mean",
        "standard_dev",
        "LOGNORMDIST"
    ],
    [
        1,
        0,
        1,
        "=LOGNORMDIST(A2, B2, C2)"
    ],
    [
        2,
        0,
        1,
        "=LOGNORMDIST(A3, B3, C3)"
    ],
    [
        4,
        3.5,
        1.2,
        "=LOGNORMDIST(A4, B4, C4)"
    ],
    [
        50,
        3.5,
        1.2,
        "=LOGNORMDIST(A5, B5, C5)"
    ]
];

    // 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('MjJhMzFhMGZjYmUzYzY5MTQwMGNhOTY1MWMyODExMzVlNDVjYzJhOTcyNjZhNGU1MzgyYjZjZGU5MDhiZWUyOGIyOGUyOGYzZjI5MzlhMTU4NDg1ZGFmYTA1Y2Q5OGY4MDcyZjlkZjhiNDgzYjI1ZDQ5ZTliNDE0NmVlMDZjYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9UYzRNekU1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "x",
        "mean",
        "standard_dev",
        "LOGNORMDIST"
    ],
    [
        1,
        0,
        1,
        "=LOGNORMDIST(A2, B2, C2)"
    ],
    [
        2,
        0,
        1,
        "=LOGNORMDIST(A3, B3, C3)"
    ],
    [
        4,
        3.5,
        1.2,
        "=LOGNORMDIST(A4, B4, C4)"
    ],
    [
        50,
        3.5,
        1.2,
        "=LOGNORMDIST(A5, B5, C5)"
    ]
]

        return {
            data
        };
    }
}
</script>

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

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MjJhMzFhMGZjYmUzYzY5MTQwMGNhOTY1MWMyODExMzVlNDVjYzJhOTcyNjZhNGU1MzgyYjZjZGU5MDhiZWUyOGIyOGUyOGYzZjI5MzlhMTU4NDg1ZGFmYTA1Y2Q5OGY4MDcyZjlkZjhiNDgzYjI1ZDQ5ZTliNDE0NmVlMDZjYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9UYzRNekU1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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: [
    [
        "x",
        "mean",
        "standard_dev",
        "LOGNORMDIST"
    ],
    [
        1,
        0,
        1,
        "=LOGNORMDIST(A2, B2, C2)"
    ],
    [
        2,
        0,
        1,
        "=LOGNORMDIST(A3, B3, C3)"
    ],
    [
        4,
        3.5,
        1.2,
        "=LOGNORMDIST(A4, B4, C4)"
    ],
    [
        50,
        3.5,
        1.2,
        "=LOGNORMDIST(A5, B5, C5)"
    ]
]
            }]
        });
    }
}