How to simulate the result of a Google Sheet multiplication formula in Javascript?

Question

If the value of f5 cell in a Google Sheet is 1.1000 (a number formatted to 4 decimal places) and the value of f6 is = f5 * 1.073, how can I ensure I get the same result multiplying those values in Javascript, eg:

var original_value = 1.1000;
var derivative_value = original_value * 1.073;

Specifically, my question is - will the result of the Javascript multiplication (derivative_value) be the same as the result of the Google formula (f6)? And if not, how can I make it so that it is?

Context / What I've Tried

For context, this question is part of a larger question I am trying to resolve for which I have set up this JSFiddle.

The JSFiddle has an input for the original_value and an input for the multiplier.

It outputs the result to four decimal places and adds trailing zeros where required (this is the required format for the result).

It is an attempt to check that the Javascript code I am writing will produce the same result as the Google Sheet formula.

[ The JSFiddle has been updated to also log decimal.js results to the console for comparison ]

Edit

There was a suggestion to use decimal.js but I'm not sure how it would be applied - something like the following?

var original_value = new Decimal(1.1000);

// some different multipliers for testing
var multiplier_a = new Decimal(1.073); 
var multiplier_b = new Decimal(1.1);

// some different results for testing
var derivative_value_a = original_value.times(multiplier_a).toString();
var derivative_value_b = original_value.times(multiplier_b).toString();

console.log(derivative_value_a);  //  1.1803
console.log(derivative_value_b);  //  1.21

Is that any more accurate than plain Javascript original_value * multiplier? More importantly for this question, will it always simulate the same result that a Google Sheet formula produces?

Answer 1

After revisiting this I have updated the jsFiddle.

The main components of what I believe are a satisfactory solution are:

• Convert both original_value and multiplier to decimal.js objects.

• Do the multiplication using the decimal.js times method.

• Do the rounding using the decimal.js toDecimalPlaces method.

• Use the argument values (4,7) to define 4 decimal places with ROUND_HALF_CEIL rounding, equivalent to Math.round (reference)

For example:

var my_decimal_js_value = new Decimal(original_value).times(new Decimal(multiplier)).toDecimalPlaces(4, 7);

In order to add any necessary trailing zeros to the result, I use:

function trailingZeros(my_decimal_js_value) {
  var result = my_decimal_js_value; 
  // add zeros if required:
  var split_result = result.toString().split(".");
  // if there are decimals present
  if (split_result[1] != undefined) {
    // declare trailing_zeros;
    var trailing_zeros;
    // get the amount of decimal numbers
    decimals_present = split_result[1].length;
    // if one decimal number, add three trailing zeros
    if (decimals_present === 1) {
      trailing_zeros = "000";
      result += trailing_zeros;
    }
    // if two decimal numbers, add two trailing zeros
    else if (decimals_present === 2) {
      trailing_zeros = "00";
      result += trailing_zeros;
    }
    // if three decimal numbers, add one trailing zero
    else if (decimals_present === 3) {
      trailing_zeros = "0";
      result += trailing_zeros;
    }
    // if four decimal numbers, just convert result to string
    else if (decimals_present === 4) {
      result = result.toString();
    }
  }
  // if there are no decimals present, add a decimal place and four zeros
  else if (split_result[1] === undefined) {
    trailing_zeros = ".0000";
    result += trailing_zeros;
  }

  return result;
}

I am still not absolutely certain that this mimics the Google Sheet multiplication formula, however using decimal.js, or another dedicated decimal library, seems to be the preferred method over plain JavaScript (to avoid possible rounding errors), based on posts such as these:

http://www.jacklmoore.com/notes/rounding-in-javascript

Is floating point math broken?

https://spin.atomicobject.com/2016/01/04/javascript-math-precision-decimals