AVERAGEIF() not summing correct arguments.

[DuckDensity]

This is:

- [X] a bug report (and proposed fix)

What is the expected behavior?

public static function AVERAGEIF($aArgs, $condition, $averageArgs = [])

Calculation of the average values by summing values in array $averageArgs where corresponding values in array $aArgs match $condition and then dividing by the number of matches.

What is the current behavior?

Values in $aArgs are summed instead of values in $averageArgs.

The current behavior is what would be expected in AVERAGE(), which is presumably where the current code came from.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php
// Include php Spreadsheet
require __DIR__ . '/vendor/autoload.php';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Some sample data
$spreadsheet->getActiveSheet()->setCellValue('B1', "5")
                              ->setCellValue('B2', "TEST")
                              ->setCellValue('B3', "12")
                              ->setCellValue('B4', "15")
                              ->setCellValue('C1', "nn")
                              ->setCellValue('C2', "nn")
                              ->setCellValue('C3', "nn")
                              ->setCellValue('C4', "ff");

// Averageif() does not calculate the correct value as it sums the first
// array and ignores the last one. In this case C1:C4 are summed, rather than B1:B4
$spreadsheet->getActiveSheet()->setCellValue('C5', "=AVERAGEIF(C1:C4,\"nn\",B1:B4)");
$spreadsheet->getActiveSheet()->setCellValue('C6', "C5 Should be 8.5");

$actual = $spreadsheet->getActiveSheet()->getCell('C5')->getCalculatedValue();
var_dump([$actual, $actual == 8.5]);

Proposed Fix

The code should be adjusted to follow the similar code that is used in SumIf()

In Statistical.php change:

  if (Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
                    if (($returnValue === null) || ($arg > $returnValue)) {
                        $returnValue += $arg;
                        ++$aCount;
                    }
                }  

to:

   if (is_numeric($averageArgs[$key])  &&
       Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
              $returnValue += $averageArgs[$key];
              ++$aCount;
         }

This alters the sum line to sum values $averageArgs and also includes the proposed fix to skip non numerical values in SumIF() #618 which applies to AverageIf() as well.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet [1.4.1]
PHP 7.1.19

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.