Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add MAXIFS, MINIFS, COUNTIFS and Remove MINIF, MAXIF #1056

Closed
zdenekdrahos opened this issue Jul 4, 2019 · 0 comments · Fixed by #1059
Closed

Add MAXIFS, MINIFS, COUNTIFS and Remove MINIF, MAXIF #1056

zdenekdrahos opened this issue Jul 4, 2019 · 0 comments · Fixed by #1059

Comments

@zdenekdrahos
Copy link
Contributor

This is:

- [x] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

Related issues: #915, #683

What is the expected behavior?

  • MINIFS, MAXIFS, COUNTIFS works
  • MINIF, MAXIF are deleted (not supported in spreadsheets) or fixed
{
    "MINIFS": "C5[5] == [5]",
    "MAXIFS": "C6[12] == [12]",
    "MINIF": "D5[5] == [5]",
    "MAXIF": "D6[12] == [12]",
    "SUMIFS": "C7[17] == [17]",
    "SUMIF": "D7[17] == [17]",
    "COUNTIFS": "C8[2] == [2]",
    "COUNTIF": "D8[2] == [2]",
}

What is the current behavior?

  • MINIFS, MAXIFS causes an exception Formula Error: Unexpected ,]
  • COUNTIFS has no result
  • MINIF, MAXIF has zero result
{
    "MINIFS": "C5[Worksheet!C5 -> Formula Error: Unexpected ,] != [5]",
    "MAXIFS": "C6[Worksheet!C6 -> Formula Error: Unexpected ,] != [12]",
    "MINIF": "D5[0] != [5]",
    "MAXIF": "D6[0] != [12]",
    "SUMIFS": "C7[17] == [17]",
    "SUMIF": "D7[17] == [17]",
    "COUNTIFS": "C8[] != [2]",
    "COUNTIF": "D8[2] == [2]",
}

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

require 'vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// code that show the issue
$spreadsheet->getActiveSheet()
    ->setCellValue('B1', "5")
    ->setCellValue('B2', "8")
    ->setCellValue('B3', "12")
    ->setCellValue('B4', "15")
    ->setCellValue('C1', 0)
    ->setCellValue('C2', 0.2)
    ->setCellValue('C3', 0)
    ->setCellValue('C4', 0.2);

$spreadsheet->getActiveSheet()
    ->setCellValue('C5', "=MINIFS(B1:B4, C1:C4, 0)")
    ->setCellValue('D5', "=MINIF(C1:C4, 0, B1:B4)")
    ->setCellValue('C6', "=MAXIFS(B1:B4, C1:C4, 0)")
    ->setCellValue('D6', "=MAXIF(C1:C4, 0, B1:B4)")
    ->setCellValue('C7', "=SUMIFS(B1:B4, C1:C4, 0)")
    ->setCellValue('D7', "=SUMIF(C1:C4, 0, B1:B4)")
    ->setCellValue('C8', "=COUNTIFS(C1:C4, 0)")
    ->setCellValue('D8', "=COUNTIF(C1:C4, 0)");

$testFile = __DIR__ . '/minifs-bug.xls';
PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx')
    ->setPreCalculateFormulas(false) // hotfix for saving file with not implemented formula
    ->save($testFile);

// https://phpspreadsheet.readthedocs.io/en/latest/references/function-list-by-name/
echo json_encode(
    [
        // 1) Not Implemented
        'MINIFS' => compareCellResult($spreadsheet, 'C5', min([5, 12])),
        'MAXIFS' => compareCellResult($spreadsheet, 'C6', max([5, 12])),
        // Implemented in phpspreadsheet, not supported in Libreoffice/GoogleSpreadsheet/Excel
        'MINIF' => compareCellResult($spreadsheet, 'D5', min([5, 12])),
        'MAXIF' => compareCellResult($spreadsheet, 'D6', max([5, 12])),
        // 2) Implemented
        'SUMIFS' => compareCellResult($spreadsheet, 'C7', array_sum([5, 12])), // OK
        'SUMIF' => compareCellResult($spreadsheet, 'D7', array_sum([5, 12])), // OK
        'COUNTIFS' => compareCellResult($spreadsheet, 'C8', count([5, 12])), // Not yet Implemented
        'COUNTIF' => compareCellResult($spreadsheet, 'D8', count([5, 12])), // OK
        'file' => $testFile
    ],
    JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES
) . "\n";

function compareCellResult($spreadsheet, $cell, $expectedResult)
{
    try {
        $cellValue = $spreadsheet->getActiveSheet()->getCell($cell)->getCalculatedValue();
    } catch (\Exception $e) {
        $cellValue = $e->getMessage();
    }
    $result = $cellValue == $expectedResult ? '==' : '!=';
    return "{$cell}[{$cellValue}] {$result} [{$expectedResult}]";
}

Which versions of PhpSpreadsheet and PHP are affected?

Tested on PhpSpreadsheet 1.7.0 and PHP 7.0.

zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 5, 2019
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 5, 2019
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 5, 2019
composer check
composer fix
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 5, 2019
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 12, 2019
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 12, 2019
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 12, 2019
composer check
composer fix
zdenekdrahos added a commit to costlocker/PhpSpreadsheet that referenced this issue Jul 12, 2019
MarkBaker pushed a commit that referenced this issue Jul 14, 2019
…NIF, MAXIF (#1059)

* #1056 - replace invalid minif/maxif functions by not implemented minifs/maxifs

minif/maxif is not support in Excel, Google Spreadsheets, Libreoffice
https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm13

* #1056 - implement minifs/maxifs

Copy-pasted sumifs...
https://github.com/PHPOffice/PhpSpreadsheet/blob/1.8.1/src/PhpSpreadsheet/Calculation/MathTrig.php#L1254

* #1056 - implement countifs

* #1056 - fix code style

composer check
composer fix

* #1056 - update changelog
PowerKiKi added a commit that referenced this issue Aug 17, 2019
1.9.0

### Added

- When &lt;br&gt; appears in a table cell, set the cell to wrap [#1071](#1071) and [#1070](#1070)
- Add MAXIFS, MINIFS, COUNTIFS and Remove MINIF, MAXIF [#1056](#1056)
- HLookup needs an ordered list even if range_lookup is set to false [#1055](#1055) and [#1076](#1076)
- Improve performance of IF function calls via ranch pruning to avoid resolution of every branches [#844](#844)
- MATCH function supports `*?~` Excel functionality, when match_type=0 [#1116](#1116)
- Allow HTML Reader to accept HTML as a string [#1136](#1136)

### Fixed

- Fix to AVERAGEIF() function when called with a third argument
- Eliminate duplicate fill none style entries [#1066](#1066)
- Fix number format masks containing literal (non-decimal point) dots [#1079](#1079)
- Fix number format masks containing named colours that were being misinterpreted as date formats; and add support for masks that fully replace the value with a full text string [#1009](#1009)
- Stricter-typed comparison testing in COUNTIF() and COUNTIFS() evaluation [#1046](#1046)
- COUPNUM should not return zero when settlement is in the last period [#1020](#1020) and [#1021](#1021)
- Fix handling of named ranges referencing sheets with spaces or "!" in their title
- Cover `getSheetByName()` with tests for name with quote and spaces [#739](#739)
- Best effort to support invalid colspan values in HTML reader - [#878](#878)
- Fixes incorrect rows deletion [#868](#868)
- MATCH function fix (value search by type, stop search when match_type=-1 and unordered element encountered) [#1116](#1116)
- Fix `getCalculatedValue()` error with more than two INDIRECT [#1115](#1115)
- Writer\Html did not hide columns [#985](#985)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
1 participant