tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Statistical / Percentiles.php

Percentiles.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/Statistical/Percentiles.php

218 lines 5.6 KB

Raw Download Zip

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