[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 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  
   8  class 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  }