[Summary view]

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

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