spreadsheet
typeScript/javascript spreadsheet parser, with formulas.
git clone https://git.vogt.world/spreadsheet.git
Log | Files | README.md
← All files
name: src/Formulas/Range.ts
-rw-r--r--
5192
  1import {
  2  ArgsChecker
  3} from "../Utilities/ArgsChecker";
  4import {
  5  Filter
  6} from "../Utilities/Filter";
  7import {
  8  TypeConverter
  9} from "../Utilities/TypeConverter";
 10import {
 11  ValueError, NAError
 12} from "../Errors";
 13import {
 14  mean
 15} from "../Utilities/MathHelpers";
 16
 17
 18/**
 19 * Calculates the frequency distribution of a range into specified classes or "bins".
 20 * @param range - to get frequency for.
 21 * @param bins - or classes.
 22 * @returns {Array<number>}
 23 * @constructor
 24 * TODO: Returns ColumnArray (values stacked in Y-direction)
 25 */
 26let FREQUENCY = function (range, bins) : Array<number> {
 27  ArgsChecker.checkLength(arguments, 2, "FREQUENCY");
 28  if (!Array.isArray(bins)) {
 29    bins = [bins];
 30  }
 31  if (!Array.isArray(range)) {
 32    range = [range];
 33  }
 34  bins = Filter.flattenAndThrow(bins).map(function (value) {
 35    return TypeConverter.firstValueAsNumber(value);
 36  }).sort(function (a, b) {
 37    return a - b;
 38  });
 39  range = Filter.flattenAndThrow(range).map(function (value) {
 40    return TypeConverter.firstValueAsNumber(value);
 41  }).sort(function (a, b) {
 42    return a - b;
 43  });
 44
 45  let n = range.length;
 46  let b = bins.length;
 47  let r = [];
 48  for (let i = 0; i <= b; i++) {
 49    r[i] = 0;
 50    for (let j = 0; j < n; j++) {
 51      if (i === 0) {
 52        if (range[j] <= bins[0]) {
 53          r[0] += 1;
 54        }
 55      } else if (i < b) {
 56        if (range[j] > bins[i - 1] && range[j] <= bins[i]) {
 57          r[i] += 1;
 58        }
 59      } else if (i === b) {
 60        if (range[j] > bins[b - 1]) {
 61          r[b] += 1;
 62        }
 63      }
 64    }
 65  }
 66  return r;
 67};
 68
 69
 70/**
 71 * Given partial data with exponential growth, fits and ideal exponential growth trend, and predicts future values. For
 72 * more information see: https://xkcd.com/1102/
 73 * @param knownY - The range or array containing the dependent, y, values that are known, and will be used to fit an
 74 * ideal exponential growth curve.
 75 * @param knownX - OPTIONAL - The range or values of the independent variables that correspond to knownY.
 76 * @param newX - OPTIONAL - The range, values, or data-points to return the y-values on the ideal curve fit.
 77 * @param shouldUseConstant - OPTIONAL - True by default. Given an exponential function y = b*m^x, should this function
 78 * calculate b?
 79 * @returns {Array}
 80 * @constructor
 81 * TODO: Returns RowArray (values stacked in X-direction)
 82 */
 83let GROWTH = function (knownY, knownX?, newX?, shouldUseConstant?) {
 84  ArgsChecker.checkLengthWithin(arguments, 1, 4, "GROWTH");
 85  // Credits: Ilmari Karonen, FormulaJs (https://github.com/sutoiku/formula.js/)
 86
 87  knownY = Filter.flattenAndThrow(knownY).map(function (value) {
 88    if (typeof value !== "number") {
 89      throw new ValueError("Function GROWTH parameter 1 expects number values. But '" + value + "' is " + (typeof value)
 90          + " and cannot be coerced to a number.");
 91    }
 92    return value;
 93  });
 94
 95  // Default values for optional parameters:
 96  if (arguments.length < 2) {
 97    knownX = [];
 98    for (let i = 1; i <= knownY.length; i++) {
 99      knownX.push(i);
100    }
101  }
102  if (arguments.length < 3) {
103    newX = [];
104    for (let i = 1; i <= knownY.length; i++) {
105      newX.push(i);
106    }
107  }
108  if (arguments.length < 4) {
109    shouldUseConstant = true || shouldUseConstant;
110  }
111
112  // Calculate sums over the data:
113  let n = knownY.length;
114  let avg_x = 0;
115  let avg_y = 0;
116  let avg_xy = 0;
117  let avg_xx = 0;
118  for (let i = 0; i < n; i++) {
119    let x = knownX[i];
120    let y = Math.log(knownY[i]);
121    avg_x += x;
122    avg_y += y;
123    avg_xy += x * y;
124    avg_xx += x * x;
125  }
126  avg_x /= n;
127  avg_y /= n;
128  avg_xy /= n;
129  avg_xx /= n;
130
131  // Compute linear regression coefficients:
132  let beta;
133  let alpha;
134  if (shouldUseConstant) {
135    beta = (avg_xy - avg_x * avg_y) / (avg_xx - avg_x * avg_x);
136    alpha = avg_y - beta * avg_x;
137  } else {
138    beta = avg_xy / avg_xx;
139    alpha = 0;
140  }
141
142  // Compute and return result array:
143  let new_y = [];
144  for (let i = 0; i < newX.length; i++) {
145    new_y.push(Math.exp(alpha + beta * newX[i]));
146  }
147  return new_y;
148};
149
150/**
151 * Returns the parameters of a linear trend.
152 * @param dataY - The range of data representing Y values.
153 * @param dataX - The range of data representing X values.
154 * @returns {number[]}
155 * @constructor
156 */
157let LINEST = function (dataY, dataX) {
158  ArgsChecker.checkLength(arguments, 2, "LINEST");
159  let rangeY = Filter.flattenAndThrow(dataY).map(function (value) {
160    return TypeConverter.valueToNumber(value);
161  });
162  let rangeX = Filter.flattenAndThrow(dataX).map(function (value) {
163    return TypeConverter.valueToNumber(value);
164  });
165
166  if (rangeX.length < 2) {
167    throw new NAError("LINEST requires more data points. Expected: 2, found: " + rangeX.length + ".");
168  }
169  if (rangeY.length < 2) {
170    throw new NAError("LINEST requires more data points. Expected: 2, found: " + rangeY.length + ".");
171  }
172
173  let xMean = mean(rangeX);
174  let yMean = mean(rangeY);
175  let n = rangeX.length;
176  let num = 0;
177  let den = 0;
178  for (let i = 0; i < n; i++) {
179    num += (rangeX[i] - xMean) * (rangeY[i] - yMean);
180    den += Math.pow(rangeX[i] - xMean, 2);
181  }
182  let m = num / den;
183  let b = yMean - m * xMean;
184  return [m, b];
185};
186
187
188export {
189  FREQUENCY,
190  GROWTH,
191  LINEST
192}