1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;
4
5use PhpOffice\PhpSpreadsheet\Calculation\Exception;
6use PhpOffice\PhpSpreadsheet\Calculation\Functions;
7
8class Percentiles
9{
10    public const RANK_SORT_DESCENDING = 0;
11
12    public const RANK_SORT_ASCENDING = 1;
13
14    /**
15     * PERCENTILE.
16     *
17     * Returns the nth percentile of values in a range..
18     *
19     * Excel Function:
20     *        PERCENTILE(value1[,value2[, ...]],entry)
21     *
22     * @param mixed $args Data values
23     *
24     * @return float|string The result, or a string containing an error
25     */
26    public static function PERCENTILE(...$args)
27    {
28        $aArgs = Functions::flattenArray($args);
29
30        // Calculate
31        $entry = array_pop($aArgs);
32
33        try {
34            $entry = StatisticalValidations::validateFloat($entry);
35        } catch (Exception $e) {
36            return $e->getMessage();
37        }
38
39        if (($entry < 0) || ($entry > 1)) {
40            return Functions::NAN();
41        }
42
43        $mArgs = self::percentileFilterValues($aArgs);
44        $mValueCount = count($mArgs);
45        if ($mValueCount > 0) {
46            sort($mArgs);
47            $count = Counts::COUNT($mArgs);
48            $index = $entry * ($count - 1);
49            $iBase = floor($index);
50            if ($index == $iBase) {
51                return $mArgs[$index];
52            }
53            $iNext = $iBase + 1;
54            $iProportion = $index - $iBase;
55
56            return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion);
57        }
58
59        return Functions::NAN();
60    }
61
62    /**
63     * PERCENTRANK.
64     *
65     * Returns the rank of a value in a data set as a percentage of the data set.
66     * Note that the returned rank is simply rounded to the appropriate significant digits,
67     *      rather than floored (as MS Excel), so value 3 for a value set of  1, 2, 3, 4 will return
68     *      0.667 rather than 0.666
69     *
70     * @param mixed $valueSet An array of (float) values, or a reference to, a list of numbers
71     * @param mixed $value The number whose rank you want to find
72     * @param mixed $significance The (integer) number of significant digits for the returned percentage value
73     *
74     * @return float|string (string if result is an error)
75     */
76    public static function PERCENTRANK($valueSet, $value, $significance = 3)
77    {
78        $valueSet = Functions::flattenArray($valueSet);
79        $value = Functions::flattenSingleValue($value);
80        $significance = ($significance === null) ? 3 : Functions::flattenSingleValue($significance);
81
82        try {
83            $value = StatisticalValidations::validateFloat($value);
84            $significance = StatisticalValidations::validateInt($significance);
85        } catch (Exception $e) {
86            return $e->getMessage();
87        }
88
89        $valueSet = self::rankFilterValues($valueSet);
90        $valueCount = count($valueSet);
91        if ($valueCount == 0) {
92            return Functions::NA();
93        }
94        sort($valueSet, SORT_NUMERIC);
95
96        $valueAdjustor = $valueCount - 1;
97        if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
98            return Functions::NA();
99        }
100
101        $pos = array_search($value, $valueSet);
102        if ($pos === false) {
103            $pos = 0;
104            $testValue = $valueSet[0];
105            while ($testValue < $value) {
106                $testValue = $valueSet[++$pos];
107            }
108            --$pos;
109            $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
110        }
111
112        return round($pos / $valueAdjustor, $significance);
113    }
114
115    /**
116     * QUARTILE.
117     *
118     * Returns the quartile of a data set.
119     *
120     * Excel Function:
121     *        QUARTILE(value1[,value2[, ...]],entry)
122     *
123     * @param mixed $args Data values
124     *
125     * @return float|string The result, or a string containing an error
126     */
127    public static function QUARTILE(...$args)
128    {
129        $aArgs = Functions::flattenArray($args);
130        $entry = array_pop($aArgs);
131
132        try {
133            $entry = StatisticalValidations::validateFloat($entry);
134        } catch (Exception $e) {
135            return $e->getMessage();
136        }
137
138        $entry = floor($entry);
139        $entry /= 4;
140        if (($entry < 0) || ($entry > 1)) {
141            return Functions::NAN();
142        }
143
144        return self::PERCENTILE($aArgs, $entry);
145    }
146
147    /**
148     * RANK.
149     *
150     * Returns the rank of a number in a list of numbers.
151     *
152     * @param mixed $value The number whose rank you want to find
153     * @param mixed $valueSet An array of float values, or a reference to, a list of numbers
154     * @param mixed $order Order to sort the values in the value set
155     *
156     * @return float|string The result, or a string containing an error (0 = Descending, 1 = Ascending)
157     */
158    public static function RANK($value, $valueSet, $order = self::RANK_SORT_DESCENDING)
159    {
160        $value = Functions::flattenSingleValue($value);
161        $valueSet = Functions::flattenArray($valueSet);
162        $order = ($order === null) ? self::RANK_SORT_DESCENDING : Functions::flattenSingleValue($order);
163
164        try {
165            $value = StatisticalValidations::validateFloat($value);
166            $order = StatisticalValidations::validateInt($order);
167        } catch (Exception $e) {
168            return $e->getMessage();
169        }
170
171        $valueSet = self::rankFilterValues($valueSet);
172        if ($order === self::RANK_SORT_DESCENDING) {
173            rsort($valueSet, SORT_NUMERIC);
174        } else {
175            sort($valueSet, SORT_NUMERIC);
176        }
177
178        $pos = array_search($value, $valueSet);
179        if ($pos === false) {
180            return Functions::NA();
181        }
182
183        return ++$pos;
184    }
185
186    protected static function percentileFilterValues(array $dataSet)
187    {
188        return array_filter(
189            $dataSet,
190            function ($value): bool {
191                return is_numeric($value) && !is_string($value);
192            }
193        );
194    }
195
196    protected static function rankFilterValues(array $dataSet)
197    {
198        return array_filter(
199            $dataSet,
200            function ($value): bool {
201                return is_numeric($value);
202            }
203        );
204    }
205}
206