Percentiles.php

Go to the documentation of this file.

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;

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

class Percentiles
{
    public const RANK_SORT_DESCENDING = 0;

    public const RANK_SORT_ASCENDING = 1;

    public static function PERCENTILE(...$args)
    {
        $aArgs = Functions::flattenArray($args);

        // Calculate
        $entry = array_pop($aArgs);

        try {
            $entry = StatisticalValidations::validateFloat($entry);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        if (($entry < 0) || ($entry > 1)) {
            return Functions::NAN();
        }

        $mArgs = self::percentileFilterValues($aArgs);
        $mValueCount = count($mArgs);
        if ($mValueCount > 0) {
            sort($mArgs);
            $count = Counts::COUNT($mArgs);
            $index = $entry * ($count - 1);
            $iBase = floor($index);
            if ($index == $iBase) {
                return $mArgs[$index];
            }
            $iNext = $iBase + 1;
            $iProportion = $index - $iBase;

            return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion);
        }

        return Functions::NAN();
    }

    public static function PERCENTRANK($valueSet, $value, $significance = 3)
    {
        $valueSet = Functions::flattenArray($valueSet);
        $value = Functions::flattenSingleValue($value);
        $significance = ($significance === null) ? 3 : Functions::flattenSingleValue($significance);

        try {
            $value = StatisticalValidations::validateFloat($value);
            $significance = StatisticalValidations::validateInt($significance);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $valueSet = self::rankFilterValues($valueSet);
        $valueCount = count($valueSet);
        if ($valueCount == 0) {
            return Functions::NA();
        }
        sort($valueSet, SORT_NUMERIC);

        $valueAdjustor = $valueCount - 1;
        if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
            return Functions::NA();
        }

        $pos = array_search($value, $valueSet);
        if ($pos === false) {
            $pos = 0;
            $testValue = $valueSet[0];
            while ($testValue < $value) {
                $testValue = $valueSet[++$pos];
            }
            --$pos;
            $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
        }

        return round($pos / $valueAdjustor, $significance);
    }

    public static function QUARTILE(...$args)
    {
        $aArgs = Functions::flattenArray($args);
        $entry = array_pop($aArgs);

        try {
            $entry = StatisticalValidations::validateFloat($entry);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $entry = floor($entry);
        $entry /= 4;
        if (($entry < 0) || ($entry > 1)) {
            return Functions::NAN();
        }

        return self::PERCENTILE($aArgs, $entry);
    }

    public static function RANK($value, $valueSet, $order = self::RANK_SORT_DESCENDING)
    {
        $value = Functions::flattenSingleValue($value);
        $valueSet = Functions::flattenArray($valueSet);
        $order = ($order === null) ? self::RANK_SORT_DESCENDING : Functions::flattenSingleValue($order);

        try {
            $value = StatisticalValidations::validateFloat($value);
            $order = StatisticalValidations::validateInt($order);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $valueSet = self::rankFilterValues($valueSet);
        if ($order === self::RANK_SORT_DESCENDING) {
            rsort($valueSet, SORT_NUMERIC);
        } else {
            sort($valueSet, SORT_NUMERIC);
        }

        $pos = array_search($value, $valueSet);
        if ($pos === false) {
            return Functions::NA();
        }

        return ++$pos;
    }

    protected static function percentileFilterValues(array $dataSet)
    {
        return array_filter(
            $dataSet,
            function ($value): bool {
                return is_numeric($value) && !is_string($value);
            }
        );
    }

    protected static function rankFilterValues(array $dataSet)
    {
        return array_filter(
            $dataSet,
            function ($value): bool {
                return is_numeric($value);
            }
        );
    }
}