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