1

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?

user1063287
  • 10,265
  • 25
  • 122
  • 218
  • Javascript can be a dangerous language to make big calculations in because of its rounding errors. If you want to be safe, maybe use a math library like: https://github.com/MikeMcl/decimal.js ? – Emil S. Jørgensen Mar 03 '17 at 09:36
  • In my understanding, I would still need to know what Google Sheet's "uses" for multiplication so that I can replicate the same behaviour. I've added my attempt at using decimal.js to the question. I've also updated the original JSFIddle to log `decimal.js` results to the console for comparison. – user1063287 Mar 04 '17 at 10:34
  • @EmilS.Jørgensen both JS and Google Sheets use the same float64 number format as far as I can tell - do you have any specification for Google Sheets that they use something different?? – Aprillion Mar 04 '17 at 11:17
  • @user1063287 please don't forget `= f5 * 1.073` is NOT the same as `= round(f5, 4) * 1.073` !!! – Aprillion Mar 04 '17 at 11:23
  • @EmilS.Jørgensen : Apart from catastrophic cancellation, you will need really big computations of about 1e11 operations to influence the 5th significant digit with accumulated floating point noise. So while you have to pay attention, I would not characterize this as "dangerous". Of course in all contract related matters one should use methods that mirror exactly the conditions and procedures outlined or implied in the contract. – Lutz Lehmann Jun 24 '17 at 11:12

2 Answers2

1

JavaScript is using so called double precision float format (64 bit)- https://tc39.github.io/ecma262/#sec-terms-and-definitions-number-value

Google Sheets seem to use the same format, you can test it by =f6*1E13 - round(f6*1E13) to see that f6 is not STORED as a fixed number format, only FORMATTED

see Number.toFixed how to FORMAT numbers in Javascript

to generate some test data:

[...Array(10)].forEach(() => {
  const f5 = 1.1
  const x = Math.random() / 100
  const f6 = f5 * x
  console.log(x, f6.toFixed(4))
})

and compare in Google Sheet:

https://docs.google.com/spreadsheets/d/1jKBwzM41nwIEyatLUHEUwteK8ImJg334hzJ8nKkUZ5M/view

=> all rounded numbers are equal.

P.S.: you need to copy the console output, paste into the Sheet, use the menu item Data > Split text into columns... > Space, then multiply by 1.1 in 3rd column and finally format all numbers

Aprillion
  • 21,510
  • 5
  • 55
  • 89
0

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

user1063287
  • 10,265
  • 25
  • 122
  • 218