L_SFROUND

=L_SFROUND(value, sig_figs, [round_opt])

Download the Template

In the template file, navigate to the General worksheet to see the L_SFROUND function in action.

Description

L_SFROUND allows you to round a value to a specific number of significant figures, rather than specifying the decimal place like the ROUND function. This function does not determine how many significant figures a value has or what formatting is used to display a number. Instead, the user needs to specify the number of digits to use for rounding and modify number formatting as needed.

Rounding to a specific number of significant figures is a common need in scientific calculations. You might know that a resulting calculation should only have 3 significiant digits, but you might not know ahead of time whether it will be 0.534 or 53.4 or 534. The built-in ROUND function may not work for you in this situation, because it requires you to specify the decimal place.

See this article for an explanation of how the L_SFROUND function determines which digit to use for rounding.

The round_opt parameter can be used to specify whether the ROUND (default), ROUNDUP, or ROUNDDOWN functions are used.

If value=0, the function returns a value of 0.

The special "5" rules for significant figures are not included in this function. (eg. 2145 rounded to 3 significant figures becomes 2150).

Lambda Formula

This code for using L_SFROUND in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Name: L_SFROUND
Comment: Round a value to a number of significant figures
Refers To:

=LAMBDA(value,sig_figs,[round_opt],
LET(doc,"https://www.vertex42.com/lambda/sfround.html",
    round_opt,IF(ISOMITTED(round_opt),0,round_opt),
    exponent,INT(LOG10(ABS(value))),
    roundto,sig_figs-(1+exponent),
    IF(value=0,0,IF(sig_figs<1,"Error: sig_figs<1",
        SWITCH(round_opt,
            0,ROUND(value,roundto),
            1,ROUNDUP(value,roundto),
            -1,ROUNDDOWN(value,roundto),
        )
    ))
))

/**
* Round a value to a number of significant figures
* round_opt: 0=ROUND, -1=ROUNDDOWN, 1=ROUNDUP
* L_SFROUND(1234500,4,-1) = 1234000
*/
L_SFROUND = LAMBDA(value,sig_figs,[round_opt],
LET(doc,"https://www.vertex42.com/lambda/sfround.html",
    round_opt,IF(ISOMITTED(round_opt),0,round_opt),
    exponent,INT(LOG10(ABS(value))),
    roundto,sig_figs-(1+exponent),
    IF(value=0,0,IF(sig_figs<1,"Error: sig_figs<1",
        SWITCH(round_opt,
            0,ROUND(value,roundto),
            1,ROUNDUP(value,roundto),
            -1,ROUNDDOWN(value,roundto),
        )
    ))
));

Name: L_SFROUND
Description: Round a value to a number of significant figures
Arguments: value, sig_figs, round_opt 
Function:

LET(doc,"https://www.vertex42.com/lambda/sfround.html",
    round_opt,IF(ISBLANK(round_opt),0,round_opt),
    exponent,INT(LOG10(ABS(value))),
    roundto,sig_figs-(1+exponent),
    IF(value=0,0,IF(sig_figs<1,"Error: sig_figs<1",
        SWITCH(round_opt,
            0,ROUND(value,roundto),
            1,ROUNDUP(value,roundto),
            -1,ROUNDDOWN(value,roundto),
        )
    ))
)

L_SFROUND Examples

Test: Copy and Paste this LET function into a cell
=LET(
    array, {0.123, 24.53, 0, 9876, -24.53},
    sig_figs, 2,
    round_opt, 0,
    L_SFROUND(array,sig_figs,round_opt)
)

Result: {0.12, 25, 0, 9900, -25}

=LET(
    array, {0.123, 24.53, 0, 9876, -24.53},
    sig_figs, 2,
    round_opt, 0,
    MAP(array,LAMBDA(cell,L_SFROUND(cell,sig_figs,round_opt)))
)

Result: {0.12, 25, 0, 9900, -25}