[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   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($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)
 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)
 198      {
 199          return array_filter(
 200              $dataSet,
 201              function ($value): bool {
 202                  return is_numeric($value);
 203              }
 204          );
 205      }
 206  }