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

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

443 lines 12.6 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Statistical;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
9	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Trend\Trend;
10
11	class Trends
12	{
13	use ArrayEnabled;
14
15	/**
16	* @param array<mixed> $array1
17	* @param array<mixed> $array2
18	*/
19	private static function filterTrendValues(array &$array1, array &$array2): void
20	{
21	foreach ($array1 as $key => $value) {
22	if ((is_bool($value)) \|\| (is_string($value)) \|\| ($value === null)) {
23	unset($array1[$key], $array2[$key]);
24	}
25	}
26	}
27
28	/**
29	* @param mixed $array1 should be array, but scalar is made into one
30	* @param mixed $array2 should be array, but scalar is made into one
31	*
32	* @param-out array<mixed> $array1
33	* @param-out array<mixed> $array2
34	*/
35	private static function checkTrendArrays(&$array1, &$array2): void
36	{
37	if (!is_array($array1)) {
38	$array1 = [$array1];
39	}
40	if (!is_array($array2)) {
41	$array2 = [$array2];
42	}
43
44	$array1 = Functions::flattenArray($array1);
45	$array2 = Functions::flattenArray($array2);
46
47	self::filterTrendValues($array1, $array2);
48	self::filterTrendValues($array2, $array1);
49
50	// Reset the array indexes
51	$array1 = array_merge($array1);
52	$array2 = array_merge($array2);
53	}
54
55	/**
56	* @param mixed[] $yValues
57	* @param mixed[] $xValues
58	*/
59	protected static function validateTrendArrays(array $yValues, array $xValues): void
60	{
61	$yValueCount = count($yValues);
62	$xValueCount = count($xValues);
63
64	if (($yValueCount === 0) \|\| ($yValueCount !== $xValueCount)) {
65	throw new Exception(ExcelError::NA());
66	} elseif ($yValueCount === 1) {
67	throw new Exception(ExcelError::DIV0());
68	}
69	}
70
71	/**
72	* CORREL.
73	*
74	* Returns covariance, the average of the products of deviations for each data point pair.
75	*
76	* @param mixed $yValues array of mixed Data Series Y
77	* @param null\|mixed $xValues array of mixed Data Series X
78	* @return float\|string
79	*/
80	public static function CORREL($yValues, $xValues = null)
81	{
82	if (($xValues === null) \|\| (!is_array($yValues)) \|\| (!is_array($xValues))) {
83	return ExcelError::VALUE();
84	}
85
86	try {
87	self::checkTrendArrays($yValues, $xValues);
88	self::validateTrendArrays($yValues, $xValues);
89	} catch (Exception $e) {
90	return $e->getMessage();
91	}
92
93	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
94
95	return $bestFitLinear->getCorrelation();
96	}
97
98	/**
99	* COVAR.
100	*
101	* Returns covariance, the average of the products of deviations for each data point pair.
102	*
103	* @param mixed[] $yValues array of mixed Data Series Y
104	* @param mixed[] $xValues array of mixed Data Series X
105	* @return float\|string
106	*/
107	public static function COVAR(array $yValues, array $xValues)
108	{
109	try {
110	self::checkTrendArrays($yValues, $xValues);
111	self::validateTrendArrays($yValues, $xValues);
112	} catch (Exception $e) {
113	return $e->getMessage();
114	}
115
116	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
117
118	return $bestFitLinear->getCovariance();
119	}
120
121	/**
122	* FORECAST.
123	*
124	* Calculates, or predicts, a future value by using existing values.
125	* The predicted value is a y-value for a given x-value.
126	*
127	* @param mixed $xValue Float value of X for which we want to find Y
128	* Or can be an array of values
129	* @param mixed[] $yValues array of mixed Data Series Y
130	* @param mixed[] $xValues array of mixed Data Series X
131	*
132	* @return array<mixed>\|bool\|float\|string If an array of numbers is passed as an argument, then the returned result will also be an array
133	* with the same dimensions
134	*/
135	public static function FORECAST($xValue, array $yValues, array $xValues)
136	{
137	if (is_array($xValue)) {
138	return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $xValue, $yValues, $xValues);
139	}
140
141	try {
142	$xValue = StatisticalValidations::validateFloat($xValue);
143	self::checkTrendArrays($yValues, $xValues);
144	self::validateTrendArrays($yValues, $xValues);
145	} catch (Exception $e) {
146	return $e->getMessage();
147	}
148
149	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
150
151	return $bestFitLinear->getValueOfYForX($xValue);
152	}
153
154	/**
155	* GROWTH.
156	*
157	* Returns values along a predicted exponential Trend
158	*
159	* @param mixed[] $yValues Data Series Y
160	* @param mixed[] $xValues Data Series X
161	* @param mixed[] $newValues Values of X for which we want to find Y
162	* @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
163	*
164	* @return array<int, array<int, array<int, float>>>
165	*/
166	public static function GROWTH(array $yValues, array $xValues = [], array $newValues = [], $const = true): array
167	{
168	$yValues = Functions::flattenArray($yValues);
169	$xValues = Functions::flattenArray($xValues);
170	$newValues = Functions::flattenArray($newValues);
171	$const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
172
173	$bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
174	if (empty($newValues)) {
175	$newValues = $bestFitExponential->getXValues();
176	}
177
178	$returnArray = [];
179	foreach ($newValues as $xValue) {
180	/** @var float $xValue */
181	$returnArray[0][] = [$bestFitExponential->getValueOfYForX($xValue)];
182	}
183
184	return $returnArray;
185	}
186
187	/**
188	* INTERCEPT.
189	*
190	* Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
191	*
192	* @param mixed[] $yValues Data Series Y
193	* @param mixed[] $xValues Data Series X
194	* @return float\|string
195	*/
196	public static function INTERCEPT(array $yValues, array $xValues)
197	{
198	try {
199	self::checkTrendArrays($yValues, $xValues);
200	self::validateTrendArrays($yValues, $xValues);
201	} catch (Exception $e) {
202	return $e->getMessage();
203	}
204
205	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
206
207	return $bestFitLinear->getIntersect();
208	}
209
210	/**
211	* LINEST.
212	*
213	* Calculates the statistics for a line by using the "least squares" method to calculate a straight line
214	* that best fits your data, and then returns an array that describes the line.
215	*
216	* @param mixed[] $yValues Data Series Y
217	* @param null\|mixed[] $xValues Data Series X
218	* @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
219	* @param mixed $stats A logical (boolean) value specifying whether to return additional regression statistics
220	*
221	* @return array<mixed>\|string The result, or a string containing an error
222	*/
223	public static function LINEST(array $yValues, ?array $xValues = null, $const = true, $stats = false)
224	{
225	$const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
226	$stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
227	if ($xValues === null) {
228	$xValues = $yValues;
229	}
230
231	try {
232	self::checkTrendArrays($yValues, $xValues);
233	self::validateTrendArrays($yValues, $xValues);
234	} catch (Exception $e) {
235	return $e->getMessage();
236	}
237
238	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
239
240	if ($stats === true) {
241	return [
242	[
243	$bestFitLinear->getSlope(),
244	$bestFitLinear->getIntersect(),
245	],
246	[
247	$bestFitLinear->getSlopeSE(),
248	($const === false) ? ExcelError::NA() : $bestFitLinear->getIntersectSE(),
249	],
250	[
251	$bestFitLinear->getGoodnessOfFit(),
252	$bestFitLinear->getStdevOfResiduals(),
253	],
254	[
255	$bestFitLinear->getF(),
256	$bestFitLinear->getDFResiduals(),
257	],
258	[
259	$bestFitLinear->getSSRegression(),
260	$bestFitLinear->getSSResiduals(),
261	],
262	];
263	}
264
265	return [
266	$bestFitLinear->getSlope(),
267	$bestFitLinear->getIntersect(),
268	];
269	}
270
271	/**
272	* LOGEST.
273	*
274	* Calculates an exponential curve that best fits the X and Y data series,
275	* and then returns an array that describes the line.
276	*
277	* @param mixed[] $yValues Data Series Y
278	* @param null\|mixed[] $xValues Data Series X
279	* @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
280	* @param mixed $stats A logical (boolean) value specifying whether to return additional regression statistics
281	*
282	* @return array<mixed>\|string The result, or a string containing an error
283	*/
284	public static function LOGEST(array $yValues, ?array $xValues = null, $const = true, $stats = false)
285	{
286	$const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
287	$stats = ($stats === null) ? false : (bool) Functions::flattenSingleValue($stats);
288	if ($xValues === null) {
289	$xValues = $yValues;
290	}
291
292	try {
293	self::checkTrendArrays($yValues, $xValues);
294	self::validateTrendArrays($yValues, $xValues);
295	} catch (Exception $e) {
296	return $e->getMessage();
297	}
298
299	foreach ($yValues as $value) {
300	if ($value < 0.0) {
301	return ExcelError::NAN();
302	}
303	}
304
305	$bestFitExponential = Trend::calculate(Trend::TREND_EXPONENTIAL, $yValues, $xValues, $const);
306
307	if ($stats === true) {
308	return [
309	[
310	$bestFitExponential->getSlope(),
311	$bestFitExponential->getIntersect(),
312	],
313	[
314	$bestFitExponential->getSlopeSE(),
315	($const === false) ? ExcelError::NA() : $bestFitExponential->getIntersectSE(),
316	],
317	[
318	$bestFitExponential->getGoodnessOfFit(),
319	$bestFitExponential->getStdevOfResiduals(),
320	],
321	[
322	$bestFitExponential->getF(),
323	$bestFitExponential->getDFResiduals(),
324	],
325	[
326	$bestFitExponential->getSSRegression(),
327	$bestFitExponential->getSSResiduals(),
328	],
329	];
330	}
331
332	return [
333	$bestFitExponential->getSlope(),
334	$bestFitExponential->getIntersect(),
335	];
336	}
337
338	/**
339	* RSQ.
340	*
341	* Returns the square of the Pearson product moment correlation coefficient through data points
342	* in known_y's and known_x's.
343	*
344	* @param mixed[] $yValues Data Series Y
345	* @param mixed[] $xValues Data Series X
346	*
347	* @return float\|string The result, or a string containing an error
348	*/
349	public static function RSQ(array $yValues, array $xValues)
350	{
351	try {
352	self::checkTrendArrays($yValues, $xValues);
353	self::validateTrendArrays($yValues, $xValues);
354	} catch (Exception $e) {
355	return $e->getMessage();
356	}
357
358	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
359
360	return $bestFitLinear->getGoodnessOfFit();
361	}
362
363	/**
364	* SLOPE.
365	*
366	* Returns the slope of the linear regression line through data points in known_y's and known_x's.
367	*
368	* @param mixed[] $yValues Data Series Y
369	* @param mixed[] $xValues Data Series X
370	*
371	* @return float\|string The result, or a string containing an error
372	*/
373	public static function SLOPE(array $yValues, array $xValues)
374	{
375	try {
376	self::checkTrendArrays($yValues, $xValues);
377	self::validateTrendArrays($yValues, $xValues);
378	} catch (Exception $e) {
379	return $e->getMessage();
380	}
381
382	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
383
384	return $bestFitLinear->getSlope();
385	}
386
387	/**
388	* STEYX.
389	*
390	* Returns the standard error of the predicted y-value for each x in the regression.
391	*
392	* @param mixed[] $yValues Data Series Y
393	* @param mixed[] $xValues Data Series X
394	* @return float\|string
395	*/
396	public static function STEYX(array $yValues, array $xValues)
397	{
398	try {
399	self::checkTrendArrays($yValues, $xValues);
400	self::validateTrendArrays($yValues, $xValues);
401	} catch (Exception $e) {
402	return $e->getMessage();
403	}
404
405	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues);
406
407	return $bestFitLinear->getStdevOfResiduals();
408	}
409
410	/**
411	* TREND.
412	*
413	* Returns values along a linear Trend
414	*
415	* @param mixed[] $yValues Data Series Y
416	* @param mixed[] $xValues Data Series X
417	* @param mixed[] $newValues Values of X for which we want to find Y
418	* @param mixed $const A logical (boolean) value specifying whether to force the intersect to equal 0 or not
419	*
420	* @return array<int, array<int, array<int, float>>>
421	*/
422	public static function TREND(array $yValues, array $xValues = [], array $newValues = [], $const = true): array
423	{
424	$yValues = Functions::flattenArray($yValues);
425	$xValues = Functions::flattenArray($xValues);
426	$newValues = Functions::flattenArray($newValues);
427	$const = ($const === null) ? true : (bool) Functions::flattenSingleValue($const);
428
429	$bestFitLinear = Trend::calculate(Trend::TREND_LINEAR, $yValues, $xValues, $const);
430	if (empty($newValues)) {
431	$newValues = $bestFitLinear->getXValues();
432	}
433
434	$returnArray = [];
435	foreach ($newValues as $xValue) {
436	/** @var float $xValue */
437	$returnArray[0][] = [$bestFitLinear->getValueOfYForX($xValue)];
438	}
439
440	return $returnArray;
441	}
442	}
443