formula_fn_math.js 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548
  1. /*
  2. ArozOS Office Sheets - math and engineering functions
  3. =====================================================
  4. Registers into the formula engine (formula.js); load after it.
  5. rounding ROUND ROUNDUP ROUNDDOWN TRUNC INT MROUND CEILING FLOOR
  6. CEILING.MATH CEILING.PRECISE ISO.CEILING FLOOR.MATH
  7. FLOOR.PRECISE EVEN ODD
  8. arithmetic ABS SIGN MOD QUOTIENT PRODUCT SUMSQ SQRT SQRTPI POWER EXP
  9. LN LOG LOG10 PI RAND RANDBETWEEN
  10. trig SIN COS TAN ASIN ACOS ATAN ATAN2 SINH COSH TANH ASINH
  11. ACOSH ATANH COT COTH ACOT ACOTH CSC CSCH SEC SECH
  12. DEGREES RADIANS
  13. integers FACT FACTDOUBLE COMBIN COMBINA GCD LCM MULTINOMIAL ISEVEN
  14. ISODD SERIESSUM BASE DECIMAL
  15. engineering BIN2DEC BIN2HEX BIN2OCT DEC2BIN DEC2HEX DEC2OCT HEX2BIN
  16. HEX2DEC HEX2OCT OCT2BIN OCT2DEC OCT2HEX BITAND BITOR BITXOR
  17. BITLSHIFT BITRSHIFT DELTA GESTEP CONVERT
  18. */
  19. (function (F) {
  20. "use strict";
  21. var ERR = F.ERR, FErr = F.FErr, isErr = F.isErr;
  22. function def(name, min, max, fn, extra) {
  23. var spec = { min: min, max: max, fn: fn, cat: "Math" };
  24. for (var k in extra || {}) spec[k] = extra[k];
  25. F.defineFunction(name, spec);
  26. }
  27. function num(msg) { return new FErr(ERR.NUM, msg); }
  28. // NaN / Infinity never leave a function
  29. function fin(v) {
  30. if (typeof v === "number" && !isFinite(v)) return num("Result is not a finite number");
  31. return v === 0 ? 0 : v; // no -0
  32. }
  33. // scaled rounding helpers that shrug off binary noise (1.005*100)
  34. function scaled(x) { return +x.toPrecision(15); }
  35. function roundTo(v, d, mode) {
  36. // negative places scale down instead of multiplying by 0.1, 0.01 ...
  37. var f = Math.pow(10, Math.abs(d));
  38. var a = scaled(d >= 0 ? Math.abs(v) * f : Math.abs(v) / f);
  39. var r = mode === "up" ? Math.ceil(a) : mode === "down" ? Math.floor(a) : Math.round(a);
  40. return fin(Math.sign(v) * (d >= 0 ? r / f : r * f));
  41. }
  42. /* one-number functions, element-wise inside array contexts */
  43. function num1(name, fn, syntax, cat) {
  44. def(name, 1, 1, function (a, E) {
  45. var x = E.num(a[0]);
  46. if (isErr(x)) return x;
  47. var r = fn(x);
  48. return isErr(r) ? r : fin(r);
  49. }, { elem: true, syntax: syntax || name + "(value)", cat: cat || "Math" });
  50. }
  51. function args2(a, E, d1) {
  52. var x = E.num(a[0]);
  53. if (isErr(x)) return x;
  54. var y = E.num(a[1], d1);
  55. if (isErr(y)) return y;
  56. return [x, y];
  57. }
  58. /* ---------- rounding ---------- */
  59. function roundFn(name, mode) {
  60. def(name, 1, 2, function (a, E) {
  61. var p = args2(a, E, 0);
  62. if (isErr(p)) return p;
  63. return roundTo(p[0], Math.trunc(p[1]), mode);
  64. }, { elem: true, syntax: name + "(value, [places])" });
  65. }
  66. roundFn("ROUND", "half");
  67. roundFn("ROUNDUP", "up");
  68. roundFn("ROUNDDOWN", "down");
  69. roundFn("TRUNC", "down");
  70. num1("INT", function (x) { return Math.floor(scaled(x)); });
  71. num1("ABS", Math.abs);
  72. num1("SIGN", Math.sign);
  73. def("MROUND", 2, 2, function (a, E) {
  74. var p = args2(a, E);
  75. if (isErr(p)) return p;
  76. var v = p[0], m = p[1];
  77. if (m === 0) return 0;
  78. if (v * m < 0) return num("MROUND value and factor must have the same sign");
  79. return fin(Math.sign(v) * Math.round(scaled(Math.abs(v / m))) * Math.abs(m));
  80. }, { elem: true, syntax: "MROUND(value, factor)" });
  81. def("CEILING", 1, 2, function (a, E) {
  82. var p = args2(a, E, 1);
  83. if (isErr(p)) return p;
  84. var v = p[0], s = p[1];
  85. if (s === 0 || v === 0) return 0;
  86. if (v > 0 && s < 0) return num("CEILING factor must be positive for a positive value");
  87. if (v < 0 && s < 0) return fin(-Math.ceil(scaled(-v / -s)) * -s);
  88. return fin(Math.ceil(scaled(v / s)) * s);
  89. }, { elem: true, syntax: "CEILING(value, [factor])" });
  90. def("FLOOR", 1, 2, function (a, E) {
  91. var p = args2(a, E, 1);
  92. if (isErr(p)) return p;
  93. var v = p[0], s = p[1];
  94. if (v === 0) return 0;
  95. if (s === 0) return new FErr(ERR.DIV0, "FLOOR factor cannot be zero");
  96. if (v > 0 && s < 0) return num("FLOOR factor must be positive for a positive value");
  97. if (v < 0 && s < 0) return fin(-Math.floor(scaled(-v / -s)) * -s);
  98. return fin(Math.floor(scaled(v / s)) * s);
  99. }, { elem: true, syntax: "FLOOR(value, [factor])" });
  100. function mathRound(name, up) {
  101. def(name, 1, 3, function (a, E) {
  102. var v = E.num(a[0]), s = E.num(a[1], 1), mode = E.num(a[2], 0);
  103. if (isErr(v)) return v;
  104. if (isErr(s)) return s;
  105. if (isErr(mode)) return mode;
  106. s = Math.abs(s);
  107. if (s === 0 || v === 0) return 0;
  108. var q = scaled(v / s);
  109. var r;
  110. if (v > 0) r = up ? Math.ceil(q) : Math.floor(q);
  111. // negative numbers: CEILING.MATH goes toward zero, FLOOR.MATH away,
  112. // unless mode flips it
  113. else if (up) r = mode ? Math.floor(q) : Math.ceil(q);
  114. else r = mode ? Math.ceil(q) : Math.floor(q);
  115. return fin(r * s);
  116. }, { elem: true, syntax: name + "(number, [significance], [mode])" });
  117. }
  118. mathRound("CEILING.MATH", true);
  119. mathRound("FLOOR.MATH", false);
  120. function preciseRound(name, up) {
  121. def(name, 1, 2, function (a, E) {
  122. var p = args2(a, E, 1);
  123. if (isErr(p)) return p;
  124. var s = Math.abs(p[1]);
  125. if (s === 0 || p[0] === 0) return 0;
  126. var q = scaled(p[0] / s);
  127. return fin((up ? Math.ceil(q) : Math.floor(q)) * s);
  128. }, { elem: true, syntax: name + "(number, [significance])" });
  129. }
  130. preciseRound("CEILING.PRECISE", true);
  131. preciseRound("ISO.CEILING", true);
  132. preciseRound("FLOOR.PRECISE", false);
  133. num1("EVEN", function (x) {
  134. var r = Math.ceil(scaled(Math.abs(x)) / 2) * 2;
  135. return Math.sign(x) * r;
  136. });
  137. num1("ODD", function (x) {
  138. var ax = Math.ceil(scaled(Math.abs(x)));
  139. if (ax % 2 === 0) ax += 1;
  140. return x < 0 ? -ax : ax;
  141. });
  142. /* ---------- arithmetic ---------- */
  143. def("MOD", 2, 2, function (a, E) {
  144. var p = args2(a, E);
  145. if (isErr(p)) return p;
  146. if (p[1] === 0) return new FErr(ERR.DIV0, "MOD by zero");
  147. // the result takes the divisor's sign, as in Excel
  148. return fin(p[0] - p[1] * Math.floor(p[0] / p[1]));
  149. }, { elem: true, syntax: "MOD(dividend, divisor)" });
  150. def("QUOTIENT", 2, 2, function (a, E) {
  151. var p = args2(a, E);
  152. if (isErr(p)) return p;
  153. if (p[1] === 0) return new FErr(ERR.DIV0, "QUOTIENT by zero");
  154. return fin(Math.trunc(scaled(p[0] / p[1])));
  155. }, { elem: true, syntax: "QUOTIENT(dividend, divisor)" });
  156. def("PRODUCT", 1, -1, function (a, E) {
  157. var st = E.numbers(a, "sum");
  158. if (st.err) return st.err;
  159. if (!st.nums.length) return 0;
  160. return fin(st.nums.reduce(function (p, x) { return p * x; }, 1));
  161. }, { syntax: "PRODUCT(factor1, [factor2, ...])" });
  162. def("SUMSQ", 1, -1, function (a, E) {
  163. var st = E.numbers(a, "sum");
  164. if (st.err) return st.err;
  165. return fin(st.nums.reduce(function (p, x) { return p + x * x; }, 0));
  166. }, { syntax: "SUMSQ(value1, [value2, ...])" });
  167. num1("SQRT", function (x) { return x < 0 ? num("SQRT of a negative number") : Math.sqrt(x); });
  168. num1("SQRTPI", function (x) { return x < 0 ? num("SQRTPI of a negative number") : Math.sqrt(x * Math.PI); });
  169. def("POWER", 2, 2, function (a, E) {
  170. var p = args2(a, E);
  171. if (isErr(p)) return p;
  172. return F.binValue("^", p[0], p[1]);
  173. }, { elem: true, syntax: "POWER(base, exponent)" });
  174. num1("EXP", Math.exp);
  175. num1("LN", function (x) { return x <= 0 ? num("LN needs a positive number") : Math.log(x); });
  176. num1("LOG10", function (x) { return x <= 0 ? num("LOG10 needs a positive number") : Math.log10(x); });
  177. def("LOG", 1, 2, function (a, E) {
  178. var p = args2(a, E, 10);
  179. if (isErr(p)) return p;
  180. if (p[0] <= 0 || p[1] <= 0) return num("LOG needs positive numbers");
  181. if (p[1] === 1) return new FErr(ERR.DIV0, "LOG base cannot be 1");
  182. return fin(Math.log(p[0]) / Math.log(p[1]));
  183. }, { elem: true, syntax: "LOG(value, [base])" });
  184. def("PI", 0, 0, function () { return Math.PI; }, { syntax: "PI()" });
  185. def("RAND", 0, 0, function () { return Math.random(); }, { volatile: true, syntax: "RAND()" });
  186. def("RANDBETWEEN", 2, 2, function (a, E) {
  187. var p = args2(a, E);
  188. if (isErr(p)) return p;
  189. var lo = Math.ceil(p[0]), hi = Math.floor(p[1]);
  190. if (lo > hi) return num("RANDBETWEEN low is above high");
  191. return lo + Math.floor(Math.random() * (hi - lo + 1));
  192. }, { elem: true, volatile: true, syntax: "RANDBETWEEN(low, high)" });
  193. /* ---------- trigonometry ---------- */
  194. function trig(name, fn, guard) {
  195. num1(name, function (x) {
  196. if (guard) {
  197. var g = guard(x);
  198. if (g) return g;
  199. }
  200. return fn(x);
  201. }, name + "(value)");
  202. }
  203. var div0 = function (msg) { return new FErr(ERR.DIV0, msg); };
  204. trig("SIN", Math.sin);
  205. trig("COS", Math.cos);
  206. trig("TAN", Math.tan);
  207. trig("ASIN", Math.asin, function (x) { return Math.abs(x) > 1 ? num("ASIN needs -1..1") : null; });
  208. trig("ACOS", Math.acos, function (x) { return Math.abs(x) > 1 ? num("ACOS needs -1..1") : null; });
  209. trig("ATAN", Math.atan);
  210. trig("SINH", Math.sinh);
  211. trig("COSH", Math.cosh);
  212. trig("TANH", Math.tanh);
  213. trig("ASINH", Math.asinh);
  214. trig("ACOSH", Math.acosh, function (x) { return x < 1 ? num("ACOSH needs a value of 1 or more") : null; });
  215. trig("ATANH", Math.atanh, function (x) { return Math.abs(x) >= 1 ? num("ATANH needs -1 < value < 1") : null; });
  216. trig("COT", function (x) { return 1 / Math.tan(x); }, function (x) { return x === 0 ? div0("COT(0)") : null; });
  217. trig("COTH", function (x) { return 1 / Math.tanh(x); }, function (x) { return x === 0 ? div0("COTH(0)") : null; });
  218. trig("ACOT", function (x) { return Math.PI / 2 - Math.atan(x); });
  219. trig("ACOTH", function (x) { return 0.5 * Math.log((x + 1) / (x - 1)); },
  220. function (x) { return Math.abs(x) <= 1 ? num("ACOTH needs |value| > 1") : null; });
  221. trig("CSC", function (x) { return 1 / Math.sin(x); }, function (x) { return x === 0 ? div0("CSC(0)") : null; });
  222. trig("CSCH", function (x) { return 1 / Math.sinh(x); }, function (x) { return x === 0 ? div0("CSCH(0)") : null; });
  223. trig("SEC", function (x) { return 1 / Math.cos(x); });
  224. trig("SECH", function (x) { return 1 / Math.cosh(x); });
  225. num1("DEGREES", function (x) { return x * 180 / Math.PI; }, "DEGREES(angle)");
  226. num1("RADIANS", function (x) { return x * Math.PI / 180; }, "RADIANS(angle)");
  227. def("ATAN2", 2, 2, function (a, E) {
  228. var p = args2(a, E);
  229. if (isErr(p)) return p;
  230. if (p[0] === 0 && p[1] === 0) return div0("ATAN2(0, 0)");
  231. return fin(Math.atan2(p[1], p[0])); // Excel order: ATAN2(x, y)
  232. }, { elem: true, syntax: "ATAN2(x, y)" });
  233. /* ---------- integers ---------- */
  234. function factorial(n) {
  235. var r = 1;
  236. for (var i = 2; i <= n; i++) r *= i;
  237. return r;
  238. }
  239. num1("FACT", function (x) {
  240. x = Math.floor(x);
  241. return x < 0 ? num("FACT of a negative number") : factorial(x);
  242. });
  243. num1("FACTDOUBLE", function (x) {
  244. x = Math.floor(x);
  245. if (x < -1) return num("FACTDOUBLE needs -1 or more");
  246. var r = 1;
  247. for (var i = x; i > 1; i -= 2) r *= i;
  248. return r;
  249. });
  250. function combin(n, k) {
  251. if (k > n - k) k = n - k;
  252. var r = 1;
  253. for (var i = 1; i <= k; i++) r = r * (n - k + i) / i;
  254. return Math.round(r);
  255. }
  256. def("COMBIN", 2, 2, function (a, E) {
  257. var p = args2(a, E);
  258. if (isErr(p)) return p;
  259. var n = Math.trunc(p[0]), k = Math.trunc(p[1]);
  260. if (n < 0 || k < 0 || k > n) return num("COMBIN needs 0 <= k <= n");
  261. return fin(combin(n, k));
  262. }, { elem: true, syntax: "COMBIN(n, k)" });
  263. def("COMBINA", 2, 2, function (a, E) {
  264. var p = args2(a, E);
  265. if (isErr(p)) return p;
  266. var n = Math.trunc(p[0]), k = Math.trunc(p[1]);
  267. if (n < 0 || k < 0 || (n === 0 && k > 0)) return num("COMBINA needs n, k >= 0");
  268. if (k === 0) return 1;
  269. return fin(combin(n + k - 1, k));
  270. }, { elem: true, syntax: "COMBINA(n, k)" });
  271. function gcd2(x, y) {
  272. while (y) { var t = y; y = x % y; x = t; }
  273. return x;
  274. }
  275. function intList(a, E, name) {
  276. var st = E.numbers(a, "sum");
  277. if (st.err) return st.err;
  278. var out = [];
  279. for (var i = 0; i < st.nums.length; i++) {
  280. var v = Math.trunc(st.nums[i]);
  281. if (v < 0) return num(name + " needs non-negative numbers");
  282. out.push(v);
  283. }
  284. return out;
  285. }
  286. def("GCD", 1, -1, function (a, E) {
  287. var l = intList(a, E, "GCD");
  288. if (isErr(l)) return l;
  289. return l.reduce(gcd2, 0);
  290. }, { syntax: "GCD(value1, [value2, ...])" });
  291. def("LCM", 1, -1, function (a, E) {
  292. var l = intList(a, E, "LCM");
  293. if (isErr(l)) return l;
  294. if (l.some(function (x) { return x === 0; })) return 0;
  295. return fin(l.reduce(function (acc, x) { return acc / gcd2(acc, x) * x; }, 1));
  296. }, { syntax: "LCM(value1, [value2, ...])" });
  297. def("MULTINOMIAL", 1, -1, function (a, E) {
  298. var l = intList(a, E, "MULTINOMIAL");
  299. if (isErr(l)) return l;
  300. var sum = 0, r = 1;
  301. l.forEach(function (x) {
  302. for (var i = 1; i <= x; i++) { sum++; r = r * sum / i; }
  303. });
  304. return fin(Math.round(r));
  305. }, { syntax: "MULTINOMIAL(value1, [value2, ...])" });
  306. num1("ISEVEN", function (x) { return Math.trunc(x) % 2 === 0; }, "ISEVEN(value)", "Info");
  307. num1("ISODD", function (x) { return Math.abs(Math.trunc(x)) % 2 === 1; }, "ISODD(value)", "Info");
  308. def("SERIESSUM", 4, 4, function (a, E) {
  309. var x = E.num(a[0]), n = E.num(a[1]), m = E.num(a[2]);
  310. var bad = [x, n, m].filter(isErr)[0];
  311. if (bad) return bad;
  312. var st = E.numbers([a[3]], "sum");
  313. if (st.err) return st.err;
  314. var s = 0;
  315. st.nums.forEach(function (c, i) { s += c * Math.pow(x, n + i * m); });
  316. return fin(s);
  317. }, { syntax: "SERIESSUM(x, n, m, a)" });
  318. def("BASE", 2, 3, function (a, E) {
  319. var v = E.num(a[0]), b = E.num(a[1]), len = E.num(a[2], 0);
  320. var bad = [v, b, len].filter(isErr)[0];
  321. if (bad) return bad;
  322. v = Math.trunc(v); b = Math.trunc(b); len = Math.trunc(len);
  323. if (v < 0 || v >= 9007199254740992 || b < 2 || b > 36 || len < 0) return num("BASE arguments out of range");
  324. var s = v.toString(b).toUpperCase();
  325. while (s.length < len) s = "0" + s;
  326. return s;
  327. }, { elem: true, syntax: "BASE(value, base, [min_length])" });
  328. def("DECIMAL", 2, 2, function (a, E) {
  329. var t = E.str(a[0]), b = E.int(a[1]);
  330. if (isErr(t)) return t;
  331. if (isErr(b)) return b;
  332. if (b < 2 || b > 36) return num("DECIMAL base must be 2..36");
  333. t = t.trim().toUpperCase();
  334. if (!t) return 0;
  335. var v = 0;
  336. for (var i = 0; i < t.length; i++) {
  337. var d = parseInt(t.charAt(i), 36);
  338. if (isNaN(d) || d >= b) return num("'" + t + "' is not a base-" + b + " number");
  339. v = v * b + d;
  340. }
  341. return fin(v);
  342. }, { elem: true, syntax: "DECIMAL(value, base)" });
  343. /* ---------- engineering: number bases (10-digit two's complement) ---------- */
  344. var BASES = {
  345. BIN: { radix: 2, re: /^[01]{1,10}$/ },
  346. OCT: { radix: 8, re: /^[0-7]{1,10}$/ },
  347. HEX: { radix: 16, re: /^[0-9A-F]{1,10}$/i },
  348. DEC: { radix: 10 }
  349. };
  350. function toDecimal(from, text) {
  351. var t = String(text).trim();
  352. var b = BASES[from];
  353. if (!b.re.test(t)) return num("'" + t + "' is not a valid " + from.toLowerCase() + " number");
  354. var v = parseInt(t, b.radix);
  355. var full = Math.pow(b.radix, 10);
  356. if (t.length === 10 && v >= full / 2) v -= full; // negative
  357. return v;
  358. }
  359. function fromDecimal(to, v, places) {
  360. var b = BASES[to], full = Math.pow(b.radix, 10);
  361. if (v < -full / 2 || v > full / 2 - 1) return num("Value is out of range for " + to);
  362. if (v < 0) return (full + v).toString(b.radix).toUpperCase();
  363. var s = v.toString(b.radix).toUpperCase();
  364. if (places !== undefined) {
  365. if (places < s.length || places > 10) return num("Not enough places");
  366. while (s.length < places) s = "0" + s;
  367. }
  368. return s;
  369. }
  370. ["BIN", "OCT", "HEX", "DEC"].forEach(function (from) {
  371. ["BIN", "OCT", "HEX", "DEC"].forEach(function (to) {
  372. if (from === to) return;
  373. var name = from + "2" + to;
  374. def(name, 1, to === "DEC" ? 1 : 2, function (a, E) {
  375. var v;
  376. if (from === "DEC") {
  377. v = E.num(a[0]);
  378. if (isErr(v)) return v;
  379. v = Math.trunc(v);
  380. } else {
  381. var raw = E.val(a[0]);
  382. if (isErr(raw)) return raw;
  383. v = toDecimal(from, typeof raw === "number" ? String(raw) : F.toStr(raw));
  384. if (isErr(v)) return v;
  385. }
  386. if (to === "DEC") return v;
  387. var places;
  388. if (!E.missing(a[1])) {
  389. places = E.int(a[1]);
  390. if (isErr(places)) return places;
  391. }
  392. return fromDecimal(to, v, places);
  393. }, { elem: true, cat: "Engineering", syntax: name + "(value" + (to === "DEC" ? "" : ", [significant_digits]") + ")" });
  394. });
  395. });
  396. /* ---------- engineering: bits (non-negative integers below 2^48) ---------- */
  397. var BIT_LIMIT = Math.pow(2, 48);
  398. function bitArg(E, node) {
  399. var v = E.num(node);
  400. if (isErr(v)) return v;
  401. if (v < 0 || v !== Math.floor(v) || v >= BIT_LIMIT) return num("Bit functions need whole numbers 0..2^48-1");
  402. return BigInt(v);
  403. }
  404. function bitFn(name, fn) {
  405. def(name, 2, 2, function (a, E) {
  406. var x = bitArg(E, a[0]);
  407. if (isErr(x)) return x;
  408. var y = bitArg(E, a[1]);
  409. if (isErr(y)) return y;
  410. return Number(fn(x, y));
  411. }, { elem: true, cat: "Engineering", syntax: name + "(value1, value2)" });
  412. }
  413. bitFn("BITAND", function (x, y) { return x & y; });
  414. bitFn("BITOR", function (x, y) { return x | y; });
  415. bitFn("BITXOR", function (x, y) { return x ^ y; });
  416. function shiftFn(name, left) {
  417. def(name, 2, 2, function (a, E) {
  418. var x = bitArg(E, a[0]);
  419. if (isErr(x)) return x;
  420. var s = E.int(a[1]);
  421. if (isErr(s)) return s;
  422. if (Math.abs(s) > 53) return num("Shift amount out of range");
  423. var amount = left ? s : -s; // negative shifts go the other way
  424. var n = amount >= 0 ? x << BigInt(amount) : x >> BigInt(-amount);
  425. if (n >= BigInt(BIT_LIMIT)) return num("Result is 2^48 or more");
  426. return Number(n);
  427. }, { elem: true, cat: "Engineering", syntax: name + "(value, shift_amount)" });
  428. }
  429. shiftFn("BITLSHIFT", true);
  430. shiftFn("BITRSHIFT", false);
  431. def("DELTA", 1, 2, function (a, E) {
  432. var p = args2(a, E, 0);
  433. if (isErr(p)) return p;
  434. return p[0] === p[1] ? 1 : 0;
  435. }, { elem: true, cat: "Engineering", syntax: "DELTA(number1, [number2])" });
  436. def("GESTEP", 1, 2, function (a, E) {
  437. var p = args2(a, E, 0);
  438. if (isErr(p)) return p;
  439. return p[0] >= p[1] ? 1 : 0;
  440. }, { elem: true, cat: "Engineering", syntax: "GESTEP(value, [step])" });
  441. /* ---------- CONVERT ---------- */
  442. /*
  443. Units by quantity, each with its size in the quantity's base unit.
  444. "p" marks units that take metric prefixes (k, M, m, u, ...); area and
  445. volume units ending in 2 / 3 apply the prefix squared / cubed.
  446. Information units also take binary prefixes (ki, Mi, ...).
  447. */
  448. var LY = 9.4607304725808e15, PICA = 0.0254 / 72;
  449. var UNITS = {
  450. mass: { g: [1, "p"], sg: [14593.9029372064], lbm: [453.59237], u: [1.660538782e-24, "p"], ozm: [28.349523125],
  451. grain: [0.06479891], cwt: [45359.237], shweight: [45359.237], uk_cwt: [50802.34544], lcwt: [50802.34544],
  452. hweight: [50802.34544], stone: [6350.29318], ton: [907184.74], uk_ton: [1016046.9088], LTON: [1016046.9088],
  453. brton: [1016046.9088] },
  454. distance: { m: [1, "p"], mi: [1609.344], Nmi: [1852], "in": [0.0254], ft: [0.3048], yd: [0.9144],
  455. ang: [1e-10, "p"], ell: [1.143], ly: [LY, "p"], parsec: [3.08567758128155e16, "p"], pc: [3.08567758128155e16, "p"],
  456. Picapt: [PICA], Pica: [PICA], pica: [0.0254 / 6], survey_mi: [1609.34721869444] },
  457. time: { yr: [31557600], day: [86400], d: [86400], hr: [3600], mn: [60], min: [60], sec: [1, "p"], s: [1, "p"] },
  458. pressure: { Pa: [1, "p"], p: [1, "p"], atm: [101325, "p"], at: [101325, "p"], mmHg: [133.322, "p"],
  459. psi: [6894.75729316836], Torr: [133.322368421053] },
  460. force: { N: [1, "p"], dyn: [1e-5, "p"], dy: [1e-5, "p"], lbf: [4.4482216152605], pond: [0.00980665, "p"] },
  461. energy: { J: [1, "p"], e: [1e-7, "p"], c: [4.184, "p"], cal: [4.1868, "p"], eV: [1.602176487e-19, "p"],
  462. ev: [1.602176487e-19, "p"], HPh: [2684519.53769617], hh: [2684519.53769617], Wh: [3600, "p"], wh: [3600, "p"],
  463. flb: [1.3558179483314], BTU: [1055.05585262], btu: [1055.05585262] },
  464. power: { HP: [745.69987158227], h: [745.69987158227], PS: [735.49875], W: [1, "p"], w: [1, "p"] },
  465. magnetism: { T: [1, "p"], ga: [1e-4, "p"] },
  466. volume: { tsp: [4.92892159375e-6], tspm: [5e-6], tbs: [1.478676478125e-5], oz: [2.95735295625e-5],
  467. cup: [2.365882365e-4], pt: [4.73176473e-4], us_pt: [4.73176473e-4], uk_pt: [5.6826125e-4],
  468. qt: [9.46352946e-4], uk_qt: [1.1365225e-3], gal: [3.785411784e-3], uk_gal: [4.54609e-3],
  469. l: [1e-3, "p"], L: [1e-3, "p"], lt: [1e-3, "p"], ang3: [1e-30, "p3"], barrel: [0.158987294928],
  470. bushel: [0.03523907016688], ft3: [0.028316846592], in3: [1.6387064e-5], ly3: [Math.pow(LY, 3), "p3"],
  471. m3: [1, "p3"], mi3: [Math.pow(1609.344, 3)], yd3: [0.764554857984], Nmi3: [Math.pow(1852, 3)],
  472. Picapt3: [Math.pow(PICA, 3)], Pica3: [Math.pow(PICA, 3)], GRT: [2.8316846592], regton: [2.8316846592],
  473. MTON: [1.13267386368] },
  474. area: { uk_acre: [4046.8564224], us_acre: [4046.87260987425], ang2: [1e-20, "p2"], ar: [100, "p"],
  475. ft2: [0.09290304], ha: [10000], in2: [0.00064516], ly2: [LY * LY, "p2"], m2: [1, "p2"], Morgen: [2500],
  476. mi2: [1609.344 * 1609.344], Nmi2: [1852 * 1852], Picapt2: [PICA * PICA], Pica2: [PICA * PICA], yd2: [0.83612736] },
  477. information: { bit: [1, "pb"], byte: [8, "pb"] },
  478. speed: { admkn: [0.514773333333333], kn: [0.514444444444444], "m/h": [1 / 3600, "p"], "m/hr": [1 / 3600, "p"],
  479. "m/s": [1, "p"], "m/sec": [1, "p"], mph: [0.44704] },
  480. temperature: { C: [0], cel: [0], F: [0], fah: [0], K: [0, "p"], kel: [0, "p"], Rank: [0], Reau: [0] }
  481. };
  482. var PREFIX = { Y: 1e24, Z: 1e21, E: 1e18, P: 1e15, T: 1e12, G: 1e9, M: 1e6, k: 1e3, h: 1e2, da: 1e1, e: 1e1,
  483. d: 1e-1, c: 1e-2, m: 1e-3, u: 1e-6, n: 1e-9, p: 1e-12, f: 1e-15, a: 1e-18, z: 1e-21, y: 1e-24 };
  484. var BIN_PREFIX = { Yi: Math.pow(2, 80), Zi: Math.pow(2, 70), Ei: Math.pow(2, 60), Pi: Math.pow(2, 50),
  485. Ti: Math.pow(2, 40), Gi: Math.pow(2, 30), Mi: Math.pow(2, 20), ki: Math.pow(2, 10) };
  486. function findUnit(sym) {
  487. var q, u;
  488. for (q in UNITS) if (Object.prototype.hasOwnProperty.call(UNITS[q], sym)) return { q: q, sym: sym, f: UNITS[q][sym][0] };
  489. // prefixed: try each prefix that the rest of the symbol accepts
  490. var tries = [];
  491. Object.keys(BIN_PREFIX).forEach(function (p) { tries.push([p, BIN_PREFIX[p], true]); });
  492. Object.keys(PREFIX).forEach(function (p) { tries.push([p, PREFIX[p], false]); });
  493. for (var i = 0; i < tries.length; i++) {
  494. var p = tries[i];
  495. if (sym.indexOf(p[0]) !== 0 || sym.length === p[0].length) continue;
  496. var rest = sym.slice(p[0].length);
  497. for (q in UNITS) {
  498. u = UNITS[q][rest];
  499. if (!u || !u[1]) continue;
  500. if (p[2] && u[1] !== "pb") continue;
  501. var power = u[1] === "p2" ? 2 : u[1] === "p3" ? 3 : 1;
  502. return { q: q, sym: rest, f: u[0] * Math.pow(p[1], power), scale: Math.pow(p[1], power) };
  503. }
  504. }
  505. return null;
  506. }
  507. function toKelvin(sym, v) {
  508. switch (sym) {
  509. case "C": case "cel": return v + 273.15;
  510. case "F": case "fah": return (v - 32) * 5 / 9 + 273.15;
  511. case "Rank": return v * 5 / 9;
  512. case "Reau": return v * 1.25 + 273.15;
  513. default: return v; // K / kel
  514. }
  515. }
  516. function fromKelvin(sym, k) {
  517. switch (sym) {
  518. case "C": case "cel": return k - 273.15;
  519. case "F": case "fah": return (k - 273.15) * 9 / 5 + 32;
  520. case "Rank": return k * 9 / 5;
  521. case "Reau": return (k - 273.15) * 0.8;
  522. default: return k;
  523. }
  524. }
  525. def("CONVERT", 3, 3, function (a, E) {
  526. var v = E.num(a[0]), fu = E.str(a[1]), tu = E.str(a[2]);
  527. var bad = [v, fu, tu].filter(isErr)[0];
  528. if (bad) return bad;
  529. var from = findUnit(fu), to = findUnit(tu);
  530. if (!from || !to) return new FErr(ERR.NA, "Unknown unit " + (!from ? fu : tu));
  531. if (from.q !== to.q) return new FErr(ERR.NA, "Cannot convert " + fu + " to " + tu);
  532. if (from.q === "temperature") {
  533. var k = toKelvin(from.sym, v * (from.scale || 1));
  534. return fin(fromKelvin(to.sym, k) / (to.scale || 1));
  535. }
  536. return fin(scaled(v * from.f / to.f));
  537. }, { elem: true, cat: "Parser", syntax: "CONVERT(value, start_unit, end_unit)" });
  538. })(typeof module !== "undefined" && module.exports ? require("./formula.js") : SheetFormula);