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

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

275 lines 6.5 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Statistical;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
7
8	class Averages extends AggregateBase
9	{
10	/**
11	* AVEDEV.
12	*
13	* Returns the average of the absolute deviations of data points from their mean.
14	* AVEDEV is a measure of the variability in a data set.
15	*
16	* Excel Function:
17	* AVEDEV(value1[,value2[, ...]])
18	*
19	* @param mixed ...$args Data values
20	*
21	* @return float\|string (string if result is an error)
22	*/
23	public static function averageDeviations(...$args)
24	{
25	$aArgs = Functions::flattenArrayIndexed($args);
26
27	// Return value
28	$returnValue = 0.0;
29
30	$aMean = self::average(...$args);
31	if ($aMean === ExcelError::DIV0()) {
32	return ExcelError::NAN();
33	} elseif ($aMean === ExcelError::VALUE()) {
34	return ExcelError::VALUE();
35	}
36
37	$aCount = 0;
38	foreach ($aArgs as $k => $arg) {
39	$arg = self::testAcceptedBoolean($arg, $k);
40	// Is it a numeric value?
41	// Strings containing numeric values are only counted if they are string literals (not cell values)
42	// and then only in MS Excel and in Open Office, not in Gnumeric
43	if ((is_string($arg)) && (!is_numeric($arg)) && (!Functions::isCellValue($k))) {
44	return ExcelError::VALUE();
45	}
46	if (self::isAcceptedCountable($arg, $k)) {
47	/** @var float\|int\|numeric-string $arg */
48	/** @var float\|int\|numeric-string $aMean */
49	$returnValue += abs($arg - $aMean);
50	++$aCount;
51	}
52	}
53
54	// Return
55	if ($aCount === 0) {
56	return ExcelError::DIV0();
57	}
58
59	return $returnValue / $aCount;
60	}
61
62	/**
63	* AVERAGE.
64	*
65	* Returns the average (arithmetic mean) of the arguments
66	*
67	* Excel Function:
68	* AVERAGE(value1[,value2[, ...]])
69	*
70	* @param mixed ...$args Data values
71	*
72	* @return float\|int\|string (string if result is an error)
73	*/
74	public static function average(...$args)
75	{
76	$returnValue = $aCount = 0;
77
78	// Loop through arguments
79	foreach (Functions::flattenArrayIndexed($args) as $k => $arg) {
80	$arg = self::testAcceptedBoolean($arg, $k);
81	// Is it a numeric value?
82	// Strings containing numeric values are only counted if they are string literals (not cell values)
83	// and then only in MS Excel and in Open Office, not in Gnumeric
84	if ((is_string($arg)) && (!is_numeric($arg)) && (!Functions::isCellValue($k))) {
85	return ExcelError::VALUE();
86	}
87	if (self::isAcceptedCountable($arg, $k)) {
88	/** @var float\|int\|numeric-string $arg */
89	$returnValue += $arg;
90	++$aCount;
91	}
92	}
93
94	// Return
95	if ($aCount > 0) {
96	return $returnValue / $aCount;
97	}
98
99	return ExcelError::DIV0();
100	}
101
102	/**
103	* AVERAGEA.
104	*
105	* Returns the average of its arguments, including numbers, text, and logical values
106	*
107	* Excel Function:
108	* AVERAGEA(value1[,value2[, ...]])
109	*
110	* @param mixed ...$args Data values
111	*
112	* @return float\|int\|string (string if result is an error)
113	*/
114	public static function averageA(...$args)
115	{
116	$returnValue = null;
117
118	$aCount = 0;
119	// Loop through arguments
120	foreach (Functions::flattenArrayIndexed($args) as $k => $arg) {
121	if (is_numeric($arg)) {
122	// do nothing
123	} elseif (is_bool($arg)) {
124	$arg = (int) $arg;
125	} elseif (!Functions::isMatrixValue($k)) {
126	$arg = 0;
127	} else {
128	return ExcelError::VALUE();
129	}
130	$returnValue += $arg;
131	++$aCount;
132	}
133
134	if ($aCount > 0) {
135	return $returnValue / $aCount;
136	}
137
138	return ExcelError::DIV0();
139	}
140
141	/**
142	* MEDIAN.
143	*
144	* Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
145	*
146	* Excel Function:
147	* MEDIAN(value1[,value2[, ...]])
148	*
149	* @param mixed ...$args Data values
150	*
151	* @return float\|string The result, or a string containing an error
152	*/
153	public static function median(...$args)
154	{
155	$aArgs = Functions::flattenArray($args);
156
157	$returnValue = ExcelError::NAN();
158
159	/** @var array<float\|int> */
160	$aArgs = self::filterArguments($aArgs);
161	$valueCount = count($aArgs);
162	if ($valueCount > 0) {
163	sort($aArgs, SORT_NUMERIC);
164	$valueCount = $valueCount / 2;
165	if ($valueCount == floor($valueCount)) {
166	$valueCount = (int) $valueCount;
167	$returnValue = ($aArgs[$valueCount--] + $aArgs[$valueCount]) / 2;
168	} else {
169	$valueCount = (int) floor($valueCount);
170	$returnValue = $aArgs[$valueCount];
171	}
172	}
173
174	return $returnValue;
175	}
176
177	/**
178	* MODE.
179	*
180	* Returns the most frequently occurring, or repetitive, value in an array or range of data
181	*
182	* Excel Function:
183	* MODE(value1[,value2[, ...]])
184	*
185	* @param mixed ...$args Data values
186	*
187	* @return float\|string The result, or a string containing an error
188	*/
189	public static function mode(...$args)
190	{
191	$returnValue = ExcelError::NA();
192
193	// Loop through arguments
194	$aArgs = Functions::flattenArray($args);
195	$aArgs = self::filterArguments($aArgs);
196
197	if (!empty($aArgs)) {
198	return self::modeCalc($aArgs);
199	}
200
201	return $returnValue;
202	}
203
204	/**
205	* @param mixed[] $args
206	*
207	* @return mixed[]
208	*/
209	protected static function filterArguments(array $args): array
210	{
211	return array_filter(
212	$args,
213	function ($value): bool {
214	// Is it a numeric value?
215	return is_numeric($value) && (!is_string($value));
216	}
217	);
218	}
219
220	/**
221	* Special variant of array_count_values that isn't limited to strings and integers,
222	* but can work with floating point numbers as values.
223	*
224	* @param mixed[] $data
225	* @return float\|string
226	*/
227	private static function modeCalc(array $data)
228	{
229	$frequencyArray = [];
230	$index = 0;
231	$maxfreq = 0;
232	$maxfreqkey = '';
233	$maxfreqdatum = '';
234	foreach ($data as $datum) {
235	/** @var float\|string $datum */
236	$found = false;
237	++$index;
238	foreach ($frequencyArray as $key => $value) {
239	/** @var string[] $value */
240	if ((string) $value['value'] == (string) $datum) {
241	++$frequencyArray[$key]['frequency'];
242	$freq = $frequencyArray[$key]['frequency'];
243	if ($freq > $maxfreq) {
244	$maxfreq = $freq;
245	$maxfreqkey = $key;
246	$maxfreqdatum = $datum;
247	} elseif ($freq == $maxfreq) {
248	if ($frequencyArray[$key]['index'] < $frequencyArray[$maxfreqkey]['index']) { //* @phpstan-ignore offsetAccess.notFound (not sure what phpstan wants)
249	$maxfreqkey = $key;
250	$maxfreqdatum = $datum;
251	}
252	}
253	$found = true;
254
255	break;
256	}
257	}
258
259	if ($found === false) {
260	$frequencyArray[] = [
261	'value' => $datum,
262	'frequency' => 1,
263	'index' => $index,
264	];
265	}
266	}
267
268	if ($maxfreq <= 1) {
269	return ExcelError::NA();
270	}
271
272	return $maxfreqdatum;
273	}
274	}
275