[Summary view]

Differences Between: [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   9  use PhpOffice\PhpSpreadsheet\Shared\Trend\Trend;
  10  
  11  class Trends
  12  {
  13      use ArrayEnabled;
  14  
  15      private static function filterTrendValues(array &$array1, array &$array2): void
  16      {
  17          foreach ($array1 as $key => $value) {
  18              if ((is_bool($value)) || (is_string($value)) || ($value === null)) {
  19                  unset($array1[$key], $array2[$key]);
  20              }
  21          }
  22      }
  23  
  24      /**
  25       * @param mixed $array1 should be array, but scalar is made into one
  26       * @param mixed $array2 should be array, but scalar is made into one
  27       */
  28      private static function checkTrendArrays(&$array1, &$array2): void
  29      {
  30          if (!is_array($array1)) {
  31              $array1 = [$array1];
  32          }
  33          if (!is_array($array2)) {
  34              $array2 = [$array2];
  35          }
  36  
  37          $array1 = Functions::flattenArray($array1);
  38          $array2 = Functions::flattenArray($array2);
  39  
  40          self::filterTrendValues($array1, $array2);
  41          self::filterTrendValues($array2, $array1);
  42  
  43          // Reset the array indexes
  44          $array1 = array_merge($array1);
  45          $array2 = array_merge($array2);
  46      }
  47  
  48      protected static function validateTrendArrays(array $yValues, array $xValues): void
  49      {
  50          $yValueCount = count($yValues);
  51          $xValueCount = count($xValues);
  52  
  53          if (($yValueCount === 0) || ($yValueCount !== $xValueCount)) {
  54              throw new Exception(ExcelError::NA());
  55          } elseif ($yValueCount === 1) {
  56              throw new Exception(ExcelError::DIV0());
  57          }
  58      }
  59  
  60      /**
  61       * CORREL.
  62       *
  63       * Returns covariance, the average of the products of deviations for each data point pair.
  64       *
  65       * @param mixed $yValues array of mixed Data Series Y
  66       * @param null|mixed $xValues array of mixed Data Series X
  67       *
  68       * @return float|string
  69       */
  70      public static function CORREL($yValues, $xValues = null)
  71      {
  72          if (($xValues === null) || (!is_array($yValues)) || (!is_array($xValues))) {
  73              return ExcelError::VALUE();
  74          }
  75  
  76          try {
  77              self::checkTrendArrays($yValues, $xValues);
  78              self::validateTrendArrays($yValues, $xValues);
  79          } catch (Exception $e) {
  80              return $e->getMessage();
  81          }
  82  
  83          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
  84  
  85          return $bestFitLinear->getCorrelation();
  86      }
  87  
  88      /**
  89       * COVAR.
  90       *
  91       * Returns covariance, the average of the products of deviations for each data point pair.
  92       *
  93       * @param mixed $yValues array of mixed Data Series Y
  94       * @param mixed $xValues array of mixed Data Series X
  95       *
  96       * @return float|string
  97       */
  98      public static function COVAR($yValues, $xValues)
  99      {
 100          try {
 101              self::checkTrendArrays($yValues, $xValues);
 102              self::validateTrendArrays($yValues, $xValues);
 103          } catch (Exception $e) {
 104              return $e->getMessage();
 105          }
 106  
 107          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 108  
 109          return $bestFitLinear->getCovariance();
 110      }
 111  
 112      /**
 113       * FORECAST.
 114       *
 115       * Calculates, or predicts, a future value by using existing values.
 116       * The predicted value is a y-value for a given x-value.
 117       *
 118       * @param mixed $xValue Float value of X for which we want to find Y
 119       *                      Or can be an array of values
 120       * @param mixed $yValues array of mixed Data Series Y
 121       * @param mixed $xValues of mixed Data Series X
 122       *
 123       * @return array|bool|float|string
 124       *         If an array of numbers is passed as an argument, then the returned result will also be an array
 125       *            with the same dimensions
 126       */
 127      public static function FORECAST($xValue, $yValues, $xValues)
 128      {
 129          if (is_array($xValue)) {
 130              return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $xValue, $yValues, $xValues);
 131          }
 132  
 133          try {
 134              $xValue = StatisticalValidations::validateFloat($xValue);
 135              self::checkTrendArrays($yValues, $xValues);
 136              self::validateTrendArrays($yValues, $xValues);
 137          } catch (Exception $e) {
 138              return $e->getMessage();
 139          }
 140  
 141          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 142  
 143          return $bestFitLinear->getValueOfYForX($xValue);
 144      }
 145  
 146      /**
 147       * GROWTH.
 148       *
 149       * Returns values along a predicted exponential Trend
 150       *
 151       * @param mixed[] $yValues Data Series Y
 152       * @param mixed[] $xValues Data Series X
 153       * @param mixed[] $newValues Values of X for which we want to find Y
 154       * @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
 155       *
 156       * @return float[]
 157       */
 158      public static function GROWTH($yValues, $xValues = [], $newValues = [], $const = true)
 159      {
 160          $yValues = Functions::flattenArray($yValues);
 161          $xValues = Functions::flattenArray($xValues);
 162          $newValues = Functions::flattenArray($newValues);
 163          $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
 164  
 165          $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
 166          if (empty($newValues)) {
 167              $newValues = $bestFitExponential->getXValues();
 168          }
 169  
 170          $returnArray = [];
 171          foreach ($newValues as $xValue) {
 172              $returnArray[0][] = [$bestFitExponential->getValueOfYForX($xValue)];
 173          }
 174  
 175          return $returnArray; //* @phpstan-ignore-line
 176      }
 177  
 178      /**
 179       * INTERCEPT.
 180       *
 181       * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
 182       *
 183       * @param mixed[] $yValues Data Series Y
 184       * @param mixed[] $xValues Data Series X
 185       *
 186       * @return float|string
 187       */
 188      public static function INTERCEPT($yValues, $xValues)
 189      {
 190          try {
 191              self::checkTrendArrays($yValues, $xValues);
 192              self::validateTrendArrays($yValues, $xValues);
 193          } catch (Exception $e) {
 194              return $e->getMessage();
 195          }
 196  
 197          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 198  
 199          return $bestFitLinear->getIntersect();
 200      }
 201  
 202      /**
 203       * LINEST.
 204       *
 205       * Calculates the statistics for a line by using the "least squares" method to calculate a straight line
 206       *     that best fits your data, and then returns an array that describes the line.
 207       *
 208       * @param mixed[] $yValues Data Series Y
 209       * @param null|mixed[] $xValues Data Series X
 210       * @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
 211       * @param mixed $stats A logical (boolean) value specifying whether to return additional regression statistics
 212       *
 213       * @return array|int|string The result, or a string containing an error
 214       */
 215      public static function LINEST($yValues, $xValues = null, $const = true, $stats = false)
 216      {
 217          $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
 218          $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
 219          if ($xValues === null) {
 220              $xValues = $yValues;
 221          }
 222  
 223          try {
 224              self::checkTrendArrays($yValues, $xValues);
 225              self::validateTrendArrays($yValues, $xValues);
 226          } catch (Exception $e) {
 227              return $e->getMessage();
 228          }
 229  
 230          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
 231  
 232          if ($stats === true) {
 233              return [
 234                  [
 235                      $bestFitLinear->getSlope(),
 236                      $bestFitLinear->getIntersect(),
 237                  ],
 238                  [
 239                      $bestFitLinear->getSlopeSE(),
 240                      ($const === false) ? ExcelError::NA() : $bestFitLinear->getIntersectSE(),
 241                  ],
 242                  [
 243                      $bestFitLinear->getGoodnessOfFit(),
 244                      $bestFitLinear->getStdevOfResiduals(),
 245                  ],
 246                  [
 247                      $bestFitLinear->getF(),
 248                      $bestFitLinear->getDFResiduals(),
 249                  ],
 250                  [
 251                      $bestFitLinear->getSSRegression(),
 252                      $bestFitLinear->getSSResiduals(),
 253                  ],
 254              ];
 255          }
 256  
 257          return [
 258              $bestFitLinear->getSlope(),
 259              $bestFitLinear->getIntersect(),
 260          ];
 261      }
 262  
 263      /**
 264       * LOGEST.
 265       *
 266       * Calculates an exponential curve that best fits the X and Y data series,
 267       *        and then returns an array that describes the line.
 268       *
 269       * @param mixed[] $yValues Data Series Y
 270       * @param null|mixed[] $xValues Data Series X
 271       * @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
 272       * @param mixed $stats A logical (boolean) value specifying whether to return additional regression statistics
 273       *
 274       * @return array|int|string The result, or a string containing an error
 275       */
 276      public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false)
 277      {
 278          $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
 279          $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
 280          if ($xValues === null) {
 281              $xValues = $yValues;
 282          }
 283  
 284          try {
 285              self::checkTrendArrays($yValues, $xValues);
 286              self::validateTrendArrays($yValues, $xValues);
 287          } catch (Exception $e) {
 288              return $e->getMessage();
 289          }
 290  
 291          foreach ($yValues as $value) {
 292              if ($value < 0.0) {
 293                  return ExcelError::NAN();
 294              }
 295          }
 296  
 297          $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
 298  
 299          if ($stats === true) {
 300              return [
 301                  [
 302                      $bestFitExponential->getSlope(),
 303                      $bestFitExponential->getIntersect(),
 304                  ],
 305                  [
 306                      $bestFitExponential->getSlopeSE(),
 307                      ($const === false) ? ExcelError::NA() : $bestFitExponential->getIntersectSE(),
 308                  ],
 309                  [
 310                      $bestFitExponential->getGoodnessOfFit(),
 311                      $bestFitExponential->getStdevOfResiduals(),
 312                  ],
 313                  [
 314                      $bestFitExponential->getF(),
 315                      $bestFitExponential->getDFResiduals(),
 316                  ],
 317                  [
 318                      $bestFitExponential->getSSRegression(),
 319                      $bestFitExponential->getSSResiduals(),
 320                  ],
 321              ];
 322          }
 323  
 324          return [
 325              $bestFitExponential->getSlope(),
 326              $bestFitExponential->getIntersect(),
 327          ];
 328      }
 329  
 330      /**
 331       * RSQ.
 332       *
 333       * Returns the square of the Pearson product moment correlation coefficient through data points
 334       *     in known_y's and known_x's.
 335       *
 336       * @param mixed[] $yValues Data Series Y
 337       * @param mixed[] $xValues Data Series X
 338       *
 339       * @return float|string The result, or a string containing an error
 340       */
 341      public static function RSQ($yValues, $xValues)
 342      {
 343          try {
 344              self::checkTrendArrays($yValues, $xValues);
 345              self::validateTrendArrays($yValues, $xValues);
 346          } catch (Exception $e) {
 347              return $e->getMessage();
 348          }
 349  
 350          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 351  
 352          return $bestFitLinear->getGoodnessOfFit();
 353      }
 354  
 355      /**
 356       * SLOPE.
 357       *
 358       * Returns the slope of the linear regression line through data points in known_y's and known_x's.
 359       *
 360       * @param mixed[] $yValues Data Series Y
 361       * @param mixed[] $xValues Data Series X
 362       *
 363       * @return float|string The result, or a string containing an error
 364       */
 365      public static function SLOPE($yValues, $xValues)
 366      {
 367          try {
 368              self::checkTrendArrays($yValues, $xValues);
 369              self::validateTrendArrays($yValues, $xValues);
 370          } catch (Exception $e) {
 371              return $e->getMessage();
 372          }
 373  
 374          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 375  
 376          return $bestFitLinear->getSlope();
 377      }
 378  
 379      /**
 380       * STEYX.
 381       *
 382       * Returns the standard error of the predicted y-value for each x in the regression.
 383       *
 384       * @param mixed[] $yValues Data Series Y
 385       * @param mixed[] $xValues Data Series X
 386       *
 387       * @return float|string
 388       */
 389      public static function STEYX($yValues, $xValues)
 390      {
 391          try {
 392              self::checkTrendArrays($yValues, $xValues);
 393              self::validateTrendArrays($yValues, $xValues);
 394          } catch (Exception $e) {
 395              return $e->getMessage();
 396          }
 397  
 398          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
 399  
 400          return $bestFitLinear->getStdevOfResiduals();
 401      }
 402  
 403      /**
 404       * TREND.
 405       *
 406       * Returns values along a linear Trend
 407       *
 408       * @param mixed[] $yValues Data Series Y
 409       * @param mixed[] $xValues Data Series X
 410       * @param mixed[] $newValues Values of X for which we want to find Y
 411       * @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
 412       *
 413       * @return float[]
 414       */
 415      public static function TREND($yValues, $xValues = [], $newValues = [], $const = true)
 416      {
 417          $yValues = Functions::flattenArray($yValues);
 418          $xValues = Functions::flattenArray($xValues);
 419          $newValues = Functions::flattenArray($newValues);
 420          $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
 421  
 422          $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
 423          if (empty($newValues)) {
 424              $newValues = $bestFitLinear->getXValues();
 425          }
 426  
 427          $returnArray = [];
 428          foreach ($newValues as $xValue) {
 429              $returnArray[0][] = [$bestFitLinear->getValueOfYForX($xValue)];
 430          }
 431  
 432          return $returnArray; //* @phpstan-ignore-line
 433      }
 434  }