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