[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 Variances extends VarianceBase
   9  {
  10      /**
  11       * VAR.
  12       *
  13       * Estimates variance based on a sample.
  14       *
  15       * Excel Function:
  16       *        VAR(value1[,value2[, ...]])
  17       *
  18       * @param mixed ...$args Data values
  19       *
  20       * @return float|string (string if result is an error)
  21       */
  22      public static function VAR(...$args)
  23      {
  24          $returnValue = ExcelError::DIV0();
  25  
  26          $summerA = $summerB = 0.0;
  27  
  28          // Loop through arguments
  29          $aArgs = Functions::flattenArray($args);
  30          $aCount = 0;
  31          foreach ($aArgs as $arg) {
  32              $arg = self::datatypeAdjustmentBooleans($arg);
  33  
  34              // Is it a numeric value?
  35              if ((is_numeric($arg)) && (!is_string($arg))) {
  36                  $summerA += ($arg * $arg);
  37                  $summerB += $arg;
  38                  ++$aCount;
  39              }
  40          }
  41  
  42          if ($aCount > 1) {
  43              $summerA *= $aCount;
  44              $summerB *= $summerB;
  45  
  46              return ($summerA - $summerB) / ($aCount * ($aCount - 1));
  47          }
  48  
  49          return $returnValue;
  50      }
  51  
  52      /**
  53       * VARA.
  54       *
  55       * Estimates variance based on a sample, including numbers, text, and logical values
  56       *
  57       * Excel Function:
  58       *        VARA(value1[,value2[, ...]])
  59       *
  60       * @param mixed ...$args Data values
  61       *
  62       * @return float|string (string if result is an error)
  63       */
  64      public static function VARA(...$args)
  65      {
  66          $returnValue = ExcelError::DIV0();
  67  
  68          $summerA = $summerB = 0.0;
  69  
  70          // Loop through arguments
  71          $aArgs = Functions::flattenArrayIndexed($args);
  72          $aCount = 0;
  73          foreach ($aArgs as $k => $arg) {
  74              if ((is_string($arg)) && (Functions::isValue($k))) {
  75                  return ExcelError::VALUE();
  76              } elseif ((is_string($arg)) && (!Functions::isMatrixValue($k))) {
  77              } else {
  78                  // Is it a numeric value?
  79                  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  80                      $arg = self::datatypeAdjustmentAllowStrings($arg);
  81                      $summerA += ($arg * $arg);
  82                      $summerB += $arg;
  83                      ++$aCount;
  84                  }
  85              }
  86          }
  87  
  88          if ($aCount > 1) {
  89              $summerA *= $aCount;
  90              $summerB *= $summerB;
  91  
  92              return ($summerA - $summerB) / ($aCount * ($aCount - 1));
  93          }
  94  
  95          return $returnValue;
  96      }
  97  
  98      /**
  99       * VARP.
 100       *
 101       * Calculates variance based on the entire population
 102       *
 103       * Excel Function:
 104       *        VARP(value1[,value2[, ...]])
 105       *
 106       * @param mixed ...$args Data values
 107       *
 108       * @return float|string (string if result is an error)
 109       */
 110      public static function VARP(...$args)
 111      {
 112          // Return value
 113          $returnValue = ExcelError::DIV0();
 114  
 115          $summerA = $summerB = 0.0;
 116  
 117          // Loop through arguments
 118          $aArgs = Functions::flattenArray($args);
 119          $aCount = 0;
 120          foreach ($aArgs as $arg) {
 121              $arg = self::datatypeAdjustmentBooleans($arg);
 122  
 123              // Is it a numeric value?
 124              if ((is_numeric($arg)) && (!is_string($arg))) {
 125                  $summerA += ($arg * $arg);
 126                  $summerB += $arg;
 127                  ++$aCount;
 128              }
 129          }
 130  
 131          if ($aCount > 0) {
 132              $summerA *= $aCount;
 133              $summerB *= $summerB;
 134  
 135              return ($summerA - $summerB) / ($aCount * $aCount);
 136          }
 137  
 138          return $returnValue;
 139      }
 140  
 141      /**
 142       * VARPA.
 143       *
 144       * Calculates variance based on the entire population, including numbers, text, and logical values
 145       *
 146       * Excel Function:
 147       *        VARPA(value1[,value2[, ...]])
 148       *
 149       * @param mixed ...$args Data values
 150       *
 151       * @return float|string (string if result is an error)
 152       */
 153      public static function VARPA(...$args)
 154      {
 155          $returnValue = ExcelError::DIV0();
 156  
 157          $summerA = $summerB = 0.0;
 158  
 159          // Loop through arguments
 160          $aArgs = Functions::flattenArrayIndexed($args);
 161          $aCount = 0;
 162          foreach ($aArgs as $k => $arg) {
 163              if ((is_string($arg)) && (Functions::isValue($k))) {
 164                  return ExcelError::VALUE();
 165              } elseif ((is_string($arg)) && (!Functions::isMatrixValue($k))) {
 166              } else {
 167                  // Is it a numeric value?
 168                  if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
 169                      $arg = self::datatypeAdjustmentAllowStrings($arg);
 170                      $summerA += ($arg * $arg);
 171                      $summerB += $arg;
 172                      ++$aCount;
 173                  }
 174              }
 175          }
 176  
 177          if ($aCount > 0) {
 178              $summerA *= $aCount;
 179              $summerB *= $summerB;
 180  
 181              return ($summerA - $summerB) / ($aCount * $aCount);
 182          }
 183  
 184          return $returnValue;
 185      }
 186  }