Trends.php

Go to the documentation of this file.

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;

use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Shared\Trend\Trend;

class Trends
{
    private static function filterTrendValues(array &$array1, array &$array2): void
    {
        foreach ($array1 as $key => $value) {
            if ((is_bool($value)) || (is_string($value)) || ($value === null)) {
                unset($array1[$key], $array2[$key]);
            }
        }
    }

    private static function checkTrendArrays(&$array1, &$array2): void
    {
        if (!is_array($array1)) {
            $array1 = [$array1];
        }
        if (!is_array($array2)) {
            $array2 = [$array2];
        }

        $array1 = Functions::flattenArray($array1);
        $array2 = Functions::flattenArray($array2);

        self::filterTrendValues($array1, $array2);
        self::filterTrendValues($array2, $array1);

        // Reset the array indexes
        $array1 = array_merge($array1);
        $array2 = array_merge($array2);
    }

    protected static function validateTrendArrays(array $yValues, array $xValues): void
    {
        $yValueCount = count($yValues);
        $xValueCount = count($xValues);

        if (($yValueCount === 0) || ($yValueCount !== $xValueCount)) {
            throw new Exception(Functions::NA());
        } elseif ($yValueCount === 1) {
            throw new Exception(Functions::DIV0());
        }
    }

    public static function CORREL($yValues, $xValues = null)
    {
        if (($xValues === null) || (!is_array($yValues)) || (!is_array($xValues))) {
            return Functions::VALUE();
        }

        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getCorrelation();
    }

    public static function COVAR($yValues, $xValues)
    {
        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getCovariance();
    }

    public static function FORECAST($xValue, $yValues, $xValues)
    {
        $xValue = Functions::flattenSingleValue($xValue);

        try {
            $xValue = StatisticalValidations::validateFloat($xValue);
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getValueOfYForX($xValue);
    }

    public static function GROWTH($yValues, $xValues = [], $newValues = [], $const = true)
    {
        $yValues = Functions::flattenArray($yValues);
        $xValues = Functions::flattenArray($xValues);
        $newValues = Functions::flattenArray($newValues);
        $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);

        $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
        if (empty($newValues)) {
            $newValues = $bestFitExponential->getXValues();
        }

        $returnArray = [];
        foreach ($newValues as $xValue) {
            $returnArray[0][] = [$bestFitExponential->getValueOfYForX($xValue)];
        }

        return $returnArray;
    }

    public static function INTERCEPT($yValues, $xValues)
    {
        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getIntersect();
    }

    public static function LINEST($yValues, $xValues = null, $const = true, $stats = false)
    {
        $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
        $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
        if ($xValues === null) {
            $xValues = $yValues;
        }

        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);

        if ($stats === true) {
            return [
                [
                    $bestFitLinear->getSlope(),
                    $bestFitLinear->getIntersect(),
                ],
                [
                    $bestFitLinear->getSlopeSE(),
                    ($const === false) ? Functions::NA() : $bestFitLinear->getIntersectSE(),
                ],
                [
                    $bestFitLinear->getGoodnessOfFit(),
                    $bestFitLinear->getStdevOfResiduals(),
                ],
                [
                    $bestFitLinear->getF(),
                    $bestFitLinear->getDFResiduals(),
                ],
                [
                    $bestFitLinear->getSSRegression(),
                    $bestFitLinear->getSSResiduals(),
                ],
            ];
        }

        return [
            $bestFitLinear->getSlope(),
            $bestFitLinear->getIntersect(),
        ];
    }

    public static function LOGEST($yValues, $xValues = null, $const = true, $stats = false)
    {
        $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
        $stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
        if ($xValues === null) {
            $xValues = $yValues;
        }

        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        foreach ($yValues as $value) {
            if ($value < 0.0) {
                return Functions::NAN();
            }
        }

        $bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);

        if ($stats === true) {
            return [
                [
                    $bestFitExponential->getSlope(),
                    $bestFitExponential->getIntersect(),
                ],
                [
                    $bestFitExponential->getSlopeSE(),
                    ($const === false) ? Functions::NA() : $bestFitExponential->getIntersectSE(),
                ],
                [
                    $bestFitExponential->getGoodnessOfFit(),
                    $bestFitExponential->getStdevOfResiduals(),
                ],
                [
                    $bestFitExponential->getF(),
                    $bestFitExponential->getDFResiduals(),
                ],
                [
                    $bestFitExponential->getSSRegression(),
                    $bestFitExponential->getSSResiduals(),
                ],
            ];
        }

        return [
            $bestFitExponential->getSlope(),
            $bestFitExponential->getIntersect(),
        ];
    }

    public static function RSQ($yValues, $xValues)
    {
        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getGoodnessOfFit();
    }

    public static function SLOPE($yValues, $xValues)
    {
        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getSlope();
    }

    public static function STEYX($yValues, $xValues)
    {
        try {
            self::checkTrendArrays($yValues, $xValues);
            self::validateTrendArrays($yValues, $xValues);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);

        return $bestFitLinear->getStdevOfResiduals();
    }

    public static function TREND($yValues, $xValues = [], $newValues = [], $const = true)
    {
        $yValues = Functions::flattenArray($yValues);
        $xValues = Functions::flattenArray($xValues);
        $newValues = Functions::flattenArray($newValues);
        $const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);

        $bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
        if (empty($newValues)) {
            $newValues = $bestFitLinear->getXValues();
        }

        $returnArray = [];
        foreach ($newValues as $xValue) {
            $returnArray[0][] = [$bestFitLinear->getValueOfYForX($xValue)];
        }

        return $returnArray;
    }
}