Skip to content

Instantly share code, notes, and snippets.

@mariohuq
Last active June 13, 2022 22:35
Show Gist options
  • Save mariohuq/0afee79de43b5f553cb2ff6ac013668e to your computer and use it in GitHub Desktop.
Save mariohuq/0afee79de43b5f553cb2ff6ac013668e to your computer and use it in GitHub Desktop.
Observational error rounding in Excel

Round a number X to N significant digits

=ROUND(X;N-(1+INT(LOG10(ABS(X)))))

Banker's rounding of number X with N decimal places

=IF(AND(ISEVEN(X*10^N);ROUND(MOD(X*10^N;1);2)<=0,5);ROUNDDOWN(X;N);ROUND(X;N))

Number of significant digits to round based on most significant digit of number X

=IF(X*10^(1-(1+INT(LOG10(ABS(X)))))<3;2;1)

Error rounding (compose these)

=IF(
  AND(
    ISEVEN(X*10^(
      IF(
        X*10^(1-(1+INT(LOG10(ABS(X)))))<3;
        2;
        1
      )-(1+INT(LOG10(ABS(X))))
    ));
    ROUND(MOD(X*10^(
      IF(
        X*10^(1-(1+INT(LOG10(ABS(X)))))<3;
        2;
        1
      )-(1+INT(LOG10(ABS(X)))));1);2)<=0,5
  );
  ROUNDDOWN(
    X;
    IF(X*10^(1-(1+INT(LOG10(ABS(X)))))<3;2;1)-(1+INT(LOG10(ABS(X))))
  );
  ROUND(
    X;
    IF(X*10^(1-(1+INT(LOG10(ABS(X)))))<3;2;1)-(1+INT(LOG10(ABS(X))))
  )
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment