[Summary view]

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

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