wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Calculation / Statistical / Percentiles.php

Percentiles.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical/Percentiles.php

207 lines 6.2 KB

Raw Download Zip

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