Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Possibly bug - FLOOR and FLOOR.MATH 
Sorry I don't use PhpSpreadsheet, but I found this bug in PHPExcel 1.8.1 and maybe it's in PhpSpreadsheet too.

I have .ods file which contains cell with formula **FLOOR.MATH**
But when I read this file and try to save to .xls I got exception:

> Formula Error: Wrong number of arguments for FLOOR() function: 3 given, 2 expected

I found that:
**FLOOR** has 2 parameters [https://support.office.com/en-us/article/floor-function-14bb497c-24f2-4e04-b327-b0b4de5a8886](url)

**FLOOR.MATH** has 3 parameters [https://support.office.com/en-us/article/floor-math-function-c302b599-fbdb-4177-ba19-2c2b1249a2f5](url)

Perhaps there is a bug, and the **FLOOR.MATH** (with 3 parameters) is recognized as **FLOOR** (with 2 parameters) 


Pull request:
Ods Handling of Ceiling and Floor
Fix #407, which went stale over 7 years ago, and is now reopened. Ods handling of CEILING and FLOOR functions is, apparently by design, subject to interoperability problems with Excel. See [FLOOR](https://wiki.documentfoundation.org/Documentation/Calc_Functions/FLOOR) and [CEILING](https://wiki.documentfoundation.org/Documentation/Calc_Functions/CEILING). Ods implements its own `CEILING.XCL` and `FLOOR.XCL` functions for interoperability purposes. PhpSpreadsheet will implement those, and `CEILING.ODS` and `FLOOR.ODS` psuedo-functions as well; none of these four will be listed in the official Functions documentation.

In all of the descriptions below, FLOOR follows CEILING exactly.

Ods writes its functions to Xml as follows:
- CEILING.XCL becomes COM.MICROSOFT.CEILING
- CEILING.MATH becomes COM.MICROSOFT.CEILING.MATH
- CEILING.PRECISE becomes COM.MICROSOFT.CEILING.PRECISE
- CEILING remains unchanged

PhpSpreadsheet Ods Writer will do the same, plus:
- CEILING.ODS becomes CEILING

PhpSpreadsheet Ods Reader will act as follows:
- CEILING (not preceded by 'COM.MICROSOFT.') becomes CEILING.ODS
- COM.MICROSOFT.CEILING.MATH becomes CEILING.MATH
- COM.MICROSOFT.CEILING.PRECISE becomes CEILING.PRECISE
- COM.MICROSOFT.CEILING becomes CEILING

PhpSpreadsheet Xlsx Writer will act as follows:
- CEILING.ODS becomes CEILING.MATH
- CEILING.XCL becomes CEILING

PhpSpreadsheet Xls Writer will recognize only the CEILING (to which it treats CEILING.XLC as an alias) and FLOOR (to which it treats FLOOR.XLC as an alias) functions.

Note that the only difference between Ods CEILING and CEILING.MATH is that CEILING will return an error if the number and significance operands have opposite signs. PhpSpreadsheet implements this as `#NUM!`.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [x] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary



Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 7900e663f..2a30d7b12 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -32,6 +32,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 - Micro-optimization for excelToDateTimeObject. [Issue #4438](https://github.com/PHPOffice/PhpSpreadsheet/issues/4438) [PR #4442](https://github.com/PHPOffice/PhpSpreadsheet/pull/4442)
 - Print Area and Row Break. [Issue #1275](https://github.com/PHPOffice/PhpSpreadsheet/issues/1275) [PR #4450](https://github.com/PHPOffice/PhpSpreadsheet/pull/4450)
 - Xls Writer Treat Hyperlink Starting with # as Internal. [Issue #56](https://github.com/PHPOffice/PhpSpreadsheet/issues/56) [PR #4453](https://github.com/PHPOffice/PhpSpreadsheet/pull/4453)
+- ODS Handling of Ceiling and Floor. [Issue #407](https://github.com/PHPOffice/PhpSpreadsheet/issues/407) [PR #4466](https://github.com/PHPOffice/PhpSpreadsheet/pull/4466)
 
 ## 2025-04-16 - 4.2.0
 
diff --git a/docs/index.md b/docs/index.md
index a4b87d32a..d577d8982 100644
--- a/docs/index.md
+++ b/docs/index.md
@@ -51,7 +51,7 @@ Use [composer](https://getcomposer.org) to install PhpSpreadsheet into your proj
 composer require phpoffice/phpspreadsheet
 ```
 
-Or also download the documentation and samples if you plan to use them (note that `git` must be in your path for this to work):
+Or also download the documentation and samples if you plan to use them:
 
 ```sh
 composer require phpoffice/phpspreadsheet --prefer-source
diff --git a/infra/DocumentGenerator.php b/infra/DocumentGenerator.php
index 85742d278..c142c434b 100644
--- a/infra/DocumentGenerator.php
+++ b/infra/DocumentGenerator.php
@@ -9,6 +9,13 @@ use UnexpectedValueException;
 
 class DocumentGenerator
 {
+    private const EXCLUDED_FUNCTIONS = [
+        'CEILING.ODS',
+        'CEILING.XCL',
+        'FLOOR.ODS',
+        'FLOOR.XCL',
+    ];
+
     /**
      * @param array<string, array{category: string, functionCall: string|string[], argumentCount: string, passCellReference?: bool, passByReference?: bool[], custom?: bool}> $phpSpreadsheetFunctions
      */
@@ -23,6 +30,9 @@ class DocumentGenerator
             $result .= self::tableRow($lengths, ['Excel Function', 'PhpSpreadsheet Function']) . "\n";
             $result .= self::tableRow($lengths, null) . "\n";
             foreach ($phpSpreadsheetFunctions as $excelFunction => $functionInfo) {
+                if (in_array($excelFunction, self::EXCLUDED_FUNCTIONS, true)) {
+                    continue;
+                }
                 if ($category === $functionInfo['category']) {
                     $phpFunction = self::getPhpSpreadsheetFunctionText($functionInfo['functionCall']);
                     $result .= self::tableRow($lengths, [$excelFunction, $phpFunction]) . "\n";
@@ -87,6 +97,9 @@ class DocumentGenerator
         $result = "# Function list by name\n";
         $lastAlphabet = null;
         foreach ($phpSpreadsheetFunctions as $excelFunction => $functionInfo) {
+            if (in_array($excelFunction, self::EXCLUDED_FUNCTIONS, true)) {
+                continue;
+            }
             $lengths = [25, 31, 37];
             if ($lastAlphabet !== $excelFunction[0]) {
                 $lastAlphabet = $excelFunction[0];
diff --git a/src/PhpSpreadsheet/Calculation/FunctionArray.php b/src/PhpSpreadsheet/Calculation/FunctionArray.php
index c7cc13b45..0692d34a8 100644
--- a/src/PhpSpreadsheet/Calculation/FunctionArray.php
+++ b/src/PhpSpreadsheet/Calculation/FunctionArray.php
@@ -277,11 +277,23 @@ class FunctionArray extends CalculationBase
             'functionCall' => [MathTrig\Ceiling::class, 'math'],
             'argumentCount' => '1-3',
         ],
+        // pseudo-function to help with Ods
+        'CEILING.ODS' => [
+            'category' => Category::CATEGORY_MATH_AND_TRIG,
+            'functionCall' => [MathTrig\Ceiling::class, 'mathOds'],
+            'argumentCount' => '1-3',
+        ],
         'CEILING.PRECISE' => [
             'category' => Category::CATEGORY_MATH_AND_TRIG,
             'functionCall' => [MathTrig\Ceiling::class, 'precise'],
             'argumentCount' => '1,2',
         ],
+        // pseudo-function implemented in Ods
+        'CEILING.XCL' => [
+            'category' => Category::CATEGORY_MATH_AND_TRIG,
+            'functionCall' => [MathTrig\Ceiling::class, 'ceiling'],
+            'argumentCount' => '2',
+        ],
         'CELL' => [
             'category' => Category::CATEGORY_INFORMATION,
             'functionCall' => [Functions::class, 'DUMMY'],
@@ -914,11 +926,23 @@ class FunctionArray extends CalculationBase
             'functionCall' => [MathTrig\Floor::class, 'math'],
             'argumentCount' => '1-3',
         ],
+        // pseudo-function to help with Ods
+        'FLOOR.ODS' => [
+            'category' => Category::CATEGORY_MATH_AND_TRIG,
+            'functionCall' => [MathTrig\Floor::class, 'mathOds'],
+            'argumentCount' => '1-3',
+        ],
         'FLOOR.PRECISE' => [
             'category' => Category::CATEGORY_MATH_AND_TRIG,
             'functionCall' => [MathTrig\Floor::class, 'precise'],
             'argumentCount' => '1-2',
         ],
+        // pseudo-function implemented in Ods
+        'FLOOR.XCL' => [
+            'category' => Category::CATEGORY_MATH_AND_TRIG,
+            'functionCall' => [MathTrig\Floor::class, 'floor'],
+            'argumentCount' => '2',
+        ],
         'FORECAST' => [
             'category' => Category::CATEGORY_STATISTICAL,
             'functionCall' => [Statistical\Trends::class, 'FORECAST'],
diff --git a/src/PhpSpreadsheet/Calculation/MathTrig/Ceiling.php b/src/PhpSpreadsheet/Calculation/MathTrig/Ceiling.php
index 365ec2e98..3458ca865 100644
--- a/src/PhpSpreadsheet/Calculation/MathTrig/Ceiling.php
+++ b/src/PhpSpreadsheet/Calculation/MathTrig/Ceiling.php
@@ -22,12 +22,12 @@ class Ceiling
      * Excel Function:
      *        CEILING(number[,significance])
      *
-     * @param array|float $number the number you want the ceiling
+     * @param array<mixed>|float $number the number you want the ceiling
      *                      Or can be an array of values
-     * @param array|float $significance the multiple to which you want to round
+     * @param array<mixed>|float $significance the multiple to which you want to round
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
@@ -63,14 +63,14 @@ class Ceiling
      *                      Or can be an array of values
      * @param mixed $significance Significance
      *                      Or can be an array of values
-     * @param array|int $mode direction to round negative numbers
+     * @param array<mixed>|int $mode direction to round negative numbers
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
-    public static function math(mixed $number, mixed $significance = null, $mode = 0): array|string|float
+    public static function math(mixed $number, mixed $significance = null, $mode = 0, bool $checkSigns = false): array|string|float
     {
         if (is_array($number) || is_array($significance) || is_array($mode)) {
             return self::evaluateArrayArguments([self::class, __FUNCTION__], $number, $significance, $mode);
@@ -87,6 +87,11 @@ class Ceiling
         if (empty($significance * $number)) {
             return 0.0;
         }
+        if ($checkSigns) {
+            if (($number > 0 && $significance < 0) || ($number < 0 && $significance > 0)) {
+                return ExcelError::NAN();
+            }
+        }
         if (self::ceilingMathTest((float) $significance, (float) $number, (int) $mode)) {
             return floor($number / $significance) * $significance;
         }
@@ -104,10 +109,10 @@ class Ceiling
      *
      * @param mixed $number the number you want to round
      *                      Or can be an array of values
-     * @param array|float $significance the multiple to which you want to round
+     * @param array<mixed>|float $significance the multiple to which you want to round
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
@@ -132,6 +137,23 @@ class Ceiling
         return ceil($result) * $significance * (($significance < 0) ? -1 : 1);
     }
 
+    /**
+     * CEILING.ODS, pseudo-function - CEILING as implemented in ODS.
+     *
+     * ODS Function (theoretical):
+     *        CEILING.ODS(number[,significance[,mode]])
+     *
+     * @param mixed $number Number to round
+     * @param mixed $significance Significance
+     * @param array<mixed>|int $mode direction to round negative numbers
+     *
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
+     */
+    public static function mathOds(mixed $number, mixed $significance = null, $mode = 0): array|string|float
+    {
+        return self::math($number, $significance, $mode, true);
+    }
+
     /**
      * Let CEILINGMATH complexity pass Scrutinizer.
      */
@@ -148,7 +170,12 @@ class Ceiling
         if (empty($number * $significance)) {
             return 0.0;
         }
-        if (Helpers::returnSign($number) == Helpers::returnSign($significance)) {
+        $signSig = Helpers::returnSign($significance);
+        $signNum = Helpers::returnSign($number);
+        if (
+            ($signSig === 1 && ($signNum === 1 || Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_GNUMERIC))
+            || ($signSig === -1 && $signNum === -1)
+        ) {
             return ceil($number / $significance) * $significance;
         }
 
diff --git a/src/PhpSpreadsheet/Calculation/MathTrig/Floor.php b/src/PhpSpreadsheet/Calculation/MathTrig/Floor.php
index 83cf0515a..b86fd45a2 100644
--- a/src/PhpSpreadsheet/Calculation/MathTrig/Floor.php
+++ b/src/PhpSpreadsheet/Calculation/MathTrig/Floor.php
@@ -32,7 +32,7 @@ class Floor
      * @param mixed $significance Expect float. Significance
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
@@ -71,11 +71,11 @@ class Floor
      * @param mixed $mode direction to round negative numbers
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
-    public static function math(mixed $number, mixed $significance = null, mixed $mode = 0)
+    public static function math(mixed $number, mixed $significance = null, mixed $mode = 0, bool $checkSigns = false)
     {
         if (is_array($number) || is_array($significance) || is_array($mode)) {
             return self::evaluateArrayArguments([self::class, __FUNCTION__], $number, $significance, $mode);
@@ -89,9 +89,37 @@ class Floor
             return $e->getMessage();
         }
 
+        if (empty($significance * $number)) {
+            return 0.0;
+        }
+        if ($checkSigns) {
+            if (($number > 0 && $significance < 0) || ($number < 0 && $significance > 0)) {
+                return ExcelError::NAN();
+            }
+        }
+
         return self::argsOk((float) $number, (float) $significance, (int) $mode);
     }
 
+    /**
+     * FLOOR.ODS, pseudo-function - FLOOR as implemented in ODS.
+     *
+     * Round a number down to the nearest integer or to the nearest multiple of significance.
+     *
+     * ODS Function (theoretical):
+     *        FLOOR.ODS(number[,significance[,mode]])
+     *
+     * @param mixed $number Number to round
+     * @param mixed $significance Significance
+     * @param array<mixed>|int $mode direction to round negative numbers
+     *
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
+     */
+    public static function mathOds(mixed $number, mixed $significance = null, mixed $mode = 0)
+    {
+        return self::math($number, $significance, $mode, true);
+    }
+
     /**
      * FLOOR.PRECISE.
      *
@@ -100,12 +128,12 @@ class Floor
      * Excel Function:
      *        FLOOR.PRECISE(number[,significance])
      *
-     * @param array|float $number Number to round
+     * @param array<mixed>|float $number Number to round
      *                      Or can be an array of values
-     * @param array|float $significance Significance
+     * @param array<mixed>|float $significance Significance
      *                      Or can be an array of values
      *
-     * @return array|float|string Rounded Number, or a string containing an error
+     * @return array<mixed>|float|string Rounded Number, or a string containing an error
      *         If an array of numbers is passed as an argument, then the returned result will also be an array
      *            with the same dimensions
      */
@@ -121,6 +149,9 @@ class Floor
         } catch (Exception $e) {
             return $e->getMessage();
         }
+        if (!$significance) {
+            return 0.0;
+        }
 
         return self::argumentsOkPrecise((float) $number, (float) $significance);
     }
@@ -179,10 +210,12 @@ class Floor
         if ($number == 0.0) {
             return 0.0;
         }
-        if (Helpers::returnSign($significance) == 1) {
-            return floor($number / $significance) * $significance;
-        }
-        if (Helpers::returnSign($number) == -1 && Helpers::returnSign($significance) == -1) {
+        $signSig = Helpers::returnSign($significance);
+        $signNum = Helpers::returnSign($number);
+        if (
+            ($signSig === 1 && ($signNum === 1 || Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_GNUMERIC))
+            || ($signNum === -1 && $signSig === -1)
+        ) {
             return floor($number / $significance) * $significance;
         }
 
diff --git a/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php b/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php
index 123f909d3..032c04a6c 100644
--- a/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php
+++ b/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php
@@ -2,6 +2,7 @@
 
 namespace PhpOffice\PhpSpreadsheet\Reader\Ods;
 
+use Composer\Pcre\Preg;
 use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
 
 class FormulaTranslator
@@ -27,7 +28,7 @@ class FormulaTranslator
         // Cell range 3-d reference
         // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
         //  and assume that the second worksheet reference is the same as the first
-        $excelAddress = (string) preg_replace(
+        $excelAddress = Preg::replace(
             [
                 '/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu',
                 '/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', // Cell range reference in another sheet
@@ -62,7 +63,7 @@ class FormulaTranslator
             //      so that conversion isn't done in string values
             $tKey = $tKey === false;
             if ($tKey) {
-                $value = (string) preg_replace(
+                $value = Preg::replace(
                     [
                         '/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference in another sheet
                         '/\[\$?([^\.]+)\.([^\.]+)\]/miu', // Cell reference in another sheet
@@ -103,7 +104,18 @@ class FormulaTranslator
                     Calculation::FORMULA_CLOSE_MATRIX_BRACE
                 );
 
-                $value = (string) preg_replace('/COM\.MICROSOFT\./ui', '', $value);
+                $value = Preg::replace(
+                    [
+                        '/\b(?<!com[.]microsoft[.])'
+                            . '(floor|ceiling)\s*[(]/ui',
+                        '/COM\.MICROSOFT\./ui',
+                    ],
+                    [
+                        '$1.ODS(',
+                        '',
+                    ],
+                    $value
+                );
             }
         }
 
diff --git a/src/PhpSpreadsheet/Writer/Ods/Formula.php b/src/PhpSpreadsheet/Writer/Ods/Formula.php
index 9364d4845..0e470b610 100644
--- a/src/PhpSpreadsheet/Writer/Ods/Formula.php
+++ b/src/PhpSpreadsheet/Writer/Ods/Formula.php
@@ -26,6 +26,7 @@ class Formula
     {
         $formula = $this->convertCellReferences($formula, $worksheetName);
         $formula = $this->convertDefinedNames($formula);
+        $formula = $this->convertFunctionNames($formula);
 
         if (!str_starts_with($formula, '=')) {
             $formula = '=' . $formula;
@@ -117,4 +118,22 @@ class Formula
 
         return $formula;
     }
+
+    private function convertFunctionNames(string $formula): string
+    {
+        return Preg::replace(
+            [
+                '/\b((CEILING|FLOOR)'
+                    . '([.](MATH|PRECISE))?)\s*[(]/ui',
+                '/\b(CEILING|FLOOR)[.]XCL\s*[(]/ui',
+                '/\b(CEILING|FLOOR)[.]ODS\s*[(]/ui',
+            ],
+            [
+                'COM.MICROSOFT.$1(',
+                'COM.MICROSOFT.$1(',
+                '$1(',
+            ],
+            $formula
+        );
+    }
 }
diff --git a/src/PhpSpreadsheet/Writer/Xls/Parser.php b/src/PhpSpreadsheet/Writer/Xls/Parser.php
index 8a16cf9c1..55a530e6c 100644
--- a/src/PhpSpreadsheet/Writer/Xls/Parser.php
+++ b/src/PhpSpreadsheet/Writer/Xls/Parser.php
@@ -410,9 +410,11 @@ class Parser
         'FISHER' => [283, 1, 1, 0],
         'FISHERINV' => [284, 1, 1, 0],
         'FLOOR' => [285, 2, 1, 0],
+        'FLOOR.XCL' => [285, 2, 1, 0],
         'GAMMADIST' => [286, 4, 1, 0],
         'GAMMAINV' => [287, 3, 1, 0],
         'CEILING' => [288, 2, 1, 0],
+        'CEILING.XCL' => [288, 2, 1, 0],
         'HYPGEOMDIST' => [289, 4, 1, 0],
         'LOGNORMDIST' => [290, 3, 1, 0],
         'LOGINV' => [291, 3, 1, 0],
diff --git a/src/PhpSpreadsheet/Writer/Xlsx/FunctionPrefix.php b/src/PhpSpreadsheet/Writer/Xlsx/FunctionPrefix.php
index 31e1e3e0b..80f0636f8 100644
--- a/src/PhpSpreadsheet/Writer/Xlsx/FunctionPrefix.php
+++ b/src/PhpSpreadsheet/Writer/Xlsx/FunctionPrefix.php
@@ -216,6 +216,18 @@ class FunctionPrefix
      */
     public static function addFunctionPrefixStripEquals(string $functionString): string
     {
+        $functionString = Preg::replace(
+            [
+                '/\b(CEILING|FLOOR)[.]ODS\s*[(]/',
+                '/\b(CEILING|FLOOR)[.]XCL\s*[(]/',
+            ],
+            [
+                '$1.MATH(',
+                '$1(',
+            ],
+            $functionString
+        );
+
         return self::addFunctionPrefix(substr($functionString, 1));
     }
 }


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/CeilingTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/CeilingTest.php
index 72cac5582..9b16ba786 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/CeilingTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/CeilingTest.php
@@ -5,10 +5,11 @@ declare(strict_types=1);
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\MathTrig;
 
 use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
+use PHPUnit\Framework\Attributes\DataProvider;
 
 class CeilingTest extends AllSetupTeardown
 {
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerCEILING')]
+    #[DataProvider('providerCEILING')]
     public function testCEILING(mixed $expectedResult, string $formula): void
     {
         $this->mightHaveException($expectedResult);
@@ -27,6 +28,24 @@ class CeilingTest extends AllSetupTeardown
         return require 'tests/data/Calculation/MathTrig/CEILING.php';
     }
 
+    public function testCEILINGGnumericClashingSigns(): void
+    {
+        self::setGnumeric();
+        $sheet = $this->getSheet();
+        $sheet->getCell('A1')->setValue('=CEILING(17,8)');
+        $result = $sheet->getCell('A1')->getCalculatedValue();
+        self::assertEqualsWithDelta(24, $result, 1E-12);
+        $sheet->getCell('A2')->setValue('=CEILING(-17,-8)');
+        $result = $sheet->getCell('A2')->getCalculatedValue();
+        self::assertEqualsWithDelta(-24, $result, 1E-12);
+        $sheet->getCell('A3')->setValue('=CEILING(17,-8)');
+        $result = $sheet->getCell('A3')->getCalculatedValue();
+        self::assertSame('#NUM!', $result);
+        $sheet->getCell('A4')->setValue('=CEILING(-17,8)');
+        $result = $sheet->getCell('A4')->getCalculatedValue();
+        self::assertSame('#NUM!', $result);
+    }
+
     public function testCEILINGGnumeric1Arg(): void
     {
         self::setGnumeric();
@@ -54,7 +73,7 @@ class CeilingTest extends AllSetupTeardown
         self::assertEqualsWithDelta(6, $result, 1E-12);
     }
 
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerCeilingArray')]
+    #[DataProvider('providerCeilingArray')]
     public function testCeilingArray(array $expectedResult, string $argument1, string $argument2): void
     {
         $calculation = Calculation::getInstance();
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/FloorTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/FloorTest.php
index df19f229e..75c749f18 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/FloorTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/FloorTest.php
@@ -5,10 +5,11 @@ declare(strict_types=1);
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\MathTrig;
 
 use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
+use PHPUnit\Framework\Attributes\DataProvider;
 
 class FloorTest extends AllSetupTeardown
 {
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerFLOOR')]
+    #[DataProvider('providerFLOOR')]
     public function testFLOOR(mixed $expectedResult, string $formula): void
     {
         $this->mightHaveException($expectedResult);
@@ -27,6 +28,24 @@ class FloorTest extends AllSetupTeardown
         return require 'tests/data/Calculation/MathTrig/FLOOR.php';
     }
 
+    public function testFLOORGnumericClashingSigns(): void
+    {
+        self::setGnumeric();
+        $sheet = $this->getSheet();
+        $sheet->getCell('A1')->setValue('=FLOOR(17,8)');
+        $result = $sheet->getCell('A1')->getCalculatedValue();
+        self::assertEqualsWithDelta(16, $result, 1E-12);
+        $sheet->getCell('A2')->setValue('=FLOOR(-17,-8)');
+        $result = $sheet->getCell('A2')->getCalculatedValue();
+        self::assertEqualsWithDelta(-16, $result, 1E-12);
+        $sheet->getCell('A3')->setValue('=FLOOR(17,-8)');
+        $result = $sheet->getCell('A3')->getCalculatedValue();
+        self::assertSame('#NUM!', $result);
+        $sheet->getCell('A4')->setValue('=FLOOR(-17,8)');
+        $result = $sheet->getCell('A4')->getCalculatedValue();
+        self::assertSame('#NUM!', $result);
+    }
+
     public function testFLOORGnumeric1Arg(): void
     {
         self::setGnumeric();
@@ -54,7 +73,7 @@ class FloorTest extends AllSetupTeardown
         self::assertEqualsWithDelta(5, $result, 1E-12);
     }
 
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerFloorArray')]
+    #[DataProvider('providerFloorArray')]
     public function testFloorArray(array $expectedResult, string $argument1, string $argument2): void
     {
         $calculation = Calculation::getInstance();
diff --git a/tests/PhpSpreadsheetTests/Reader/Ods/CeilingFloorTest.php b/tests/PhpSpreadsheetTests/Reader/Ods/CeilingFloorTest.php
new file mode 100644
index 000000000..2d1f1ae98
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Reader/Ods/CeilingFloorTest.php
@@ -0,0 +1,120 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Reader\Ods;
+
+use PhpOffice\PhpSpreadsheet\Reader\Ods as OdsReader;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
+
+class CeilingFloorTest extends AbstractFunctional
+{
+    private const INFILE = 'tests/data/Reader/Ods/issue.407.ods';
+
+    private const EXPECTED_ODS = [
+        'A1' => '=FLOOR(2.5,1)',
+        'A2' => '=FLOOR.MATH(2.5,1)',
+        'A3' => '=FLOOR.PRECISE(2.5,1)',
+        'A4' => '=FLOOR.ODS(2.5,1,0)',
+        'C1' => '=CEILING(2.5,1)',
+        'C2' => '=CEILING.MATH(2.5,1)',
+        'C3' => '=CEILING.PRECISE(2.5,1)',
+        'C4' => '=CEILING.ODS(2.5,1)',
+        'E1' => '=FLOOR.ODS(37, -1, 0)',
+        'F1' => '=CEILING.ODS(-37.2,1,0)',
+    ];
+
+    private const EXPECTED_XLSX = [
+        'A1' => '=FLOOR(2.5,1)',
+        'A2' => '=FLOOR.MATH(2.5,1)',
+        'A3' => '=FLOOR.PRECISE(2.5,1)',
+        'A4' => '=FLOOR.MATH(2.5,1,0)', // note different from ODS
+        'C1' => '=CEILING(2.5,1)',
+        'C2' => '=CEILING.MATH(2.5,1)',
+        'C3' => '=CEILING.PRECISE(2.5,1)',
+        'C4' => '=CEILING.MATH(2.5,1)', // different from ODS
+        'E1' => '=FLOOR.MATH(37, -1, 0)', // different from ODS
+        'F1' => '=CEILING.MATH(-37.2,1,0)', // different from ODS
+    ];
+
+    public function testReadAndWriteOds(): void
+    {
+        $reader = new OdsReader();
+        $spreadsheetOld = $reader->load(self::INFILE);
+        $oldSheet = $spreadsheetOld->getActiveSheet();
+        foreach (self::EXPECTED_ODS as $key => $value) {
+            self::assertSame(
+                $value,
+                $oldSheet->getCell($key)->getValue(),
+                "Error in cell $key"
+            );
+        }
+        self::assertSame('#NUM!', $oldSheet->getCell('E1')->getCalculatedValue());
+        self::assertSame('#NUM!', $oldSheet->getCell('F1')->getCalculatedValue());
+
+        $spreadsheet = $this->writeAndReload($spreadsheetOld, 'Ods');
+        $spreadsheetOld->disconnectWorksheets();
+        $sheet = $spreadsheet->getActiveSheet();
+        foreach (self::EXPECTED_ODS as $key => $value) {
+            self::assertSame(
+                $value,
+                $sheet->getCell($key)->getValue(),
+                "Error in cell $key"
+            );
+        }
+        self::assertSame('#NUM!', $sheet->getCell('E1')->getCalculatedValue());
+        self::assertSame('#NUM!', $sheet->getCell('F1')->getCalculatedValue());
+
+        $spreadsheet->disconnectWorksheets();
+    }
+
+    public function testReadAndWriteXlsx(): void
+    {
+        $reader = new OdsReader();
+        $spreadsheetOld = $reader->load(self::INFILE);
+
+        $spreadsheet = $this->writeAndReload($spreadsheetOld, 'Xlsx');
+        $spreadsheetOld->disconnectWorksheets();
+        $sheet = $spreadsheet->getActiveSheet();
+        foreach (self::EXPECTED_XLSX as $key => $value) {
+            self::assertSame(
+                $value,
+                $sheet->getCell($key)->getValue(),
+                "Error in cell $key"
+            );
+        }
+        self::assertSame(37.0, $sheet->getCell('E1')->getCalculatedValue()); // different from ODS
+        self::assertSame(-37.0, $sheet->getCell('F1')->getCalculatedValue()); // different from ODS
+
+        $spreadsheet->disconnectWorksheets();
+    }
+
+    public function testWriteXcl(): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->getCell('A1')->setValue('=CEILING.XCL(17.8,8)');
+        $sheet->getCell('B1')->setValue('=FLOOR.XCL(17.8,8)');
+
+        $spreadsheetOds = $this->writeAndReload($spreadsheet, 'Ods');
+        $sheetOds = $spreadsheetOds->getActiveSheet();
+        self::assertSame('=CEILING(17.8,8)', $sheetOds->getCell('A1')->getValue());
+        self::assertSame('=FLOOR(17.8,8)', $sheetOds->getCell('B1')->getValue());
+        $spreadsheetOds->disconnectWorksheets();
+
+        $spreadsheetXlsx = $this->writeAndReload($spreadsheet, 'Xlsx');
+        $sheetXlsx = $spreadsheetXlsx->getActiveSheet();
+        self::assertSame('=CEILING(17.8,8)', $sheetXlsx->getCell('A1')->getValue());
+        self::assertSame('=FLOOR(17.8,8)', $sheetXlsx->getCell('B1')->getValue());
+        $spreadsheetXlsx->disconnectWorksheets();
+
+        $spreadsheetXls = $this->writeAndReload($spreadsheet, 'Xls');
+        $sheetXls = $spreadsheetXls->getActiveSheet();
+        self::assertSame('=CEILING(17.8,8)', $sheetXls->getCell('A1')->getValue());
+        self::assertSame('=FLOOR(17.8,8)', $sheetXls->getCell('B1')->getValue());
+        $spreadsheetXls->disconnectWorksheets();
+
+        $spreadsheet->disconnectWorksheets();
+    }
+}
diff --git a/tests/data/Calculation/MathTrig/CEILING.php b/tests/data/Calculation/MathTrig/CEILING.php
index c2adf59a6..844da91a9 100644
--- a/tests/data/Calculation/MathTrig/CEILING.php
+++ b/tests/data/Calculation/MathTrig/CEILING.php
@@ -11,7 +11,7 @@ return [
     [0.0, '8, 0'],
     [9.0, '8, 1.5'],
     ['#NUM!', '8, -1.5'],
-    ['#NUM!', '-8, 1.5'],
+    'changed with pr 4466 #1' => [-7.5, '-8, 1.5'],
     [-9.0, '-8, -1.5'],
     [8.3, '8.26, 0.05'],
     [2.35, '2.341, 0.05'],
@@ -23,7 +23,7 @@ return [
     [210.70, '210.67, 0.05'],
     [210.65, '210.63, 0.05'],
     [4.0, '2.98, 2'],
-    ['#NUM!', '-2.98, 2'],
+    'changed with pr 4466 #2' => [-2, '-2.98, 2'],
     [-5.0, '-4.5, -1'],
     [0, ',1'],
     [0, 'false,1'],
diff --git a/tests/data/Calculation/MathTrig/FLOOR.php b/tests/data/Calculation/MathTrig/FLOOR.php
index ec2aa87f8..b247e6ce4 100644
--- a/tests/data/Calculation/MathTrig/FLOOR.php
+++ b/tests/data/Calculation/MathTrig/FLOOR.php
@@ -8,6 +8,7 @@ return [
     [-4, '-2.5, 2'],
     [0.0, '0.0, 1'],
     ['#NUM!', '2.5, -2'],
+    [-4, '-2.5, 2'],
     ['#DIV/0!', '123.456, 0'],
     [1.5, '1.5, 0.1'],
     [0.23, '0.234, 0.01'],
diff --git a/tests/data/Calculation/MathTrig/FLOORMATH.php b/tests/data/Calculation/MathTrig/FLOORMATH.php
index d8b1303fb..bf437105f 100644
--- a/tests/data/Calculation/MathTrig/FLOORMATH.php
+++ b/tests/data/Calculation/MathTrig/FLOORMATH.php
@@ -8,7 +8,7 @@ return [
     [-4, '-2.5, 2'],
     [2, '2.5, -2'],
     [0.0, '0.0, 1'],
-    ['#DIV/0!', '123.456, 0'],
+    'corrected with PR 4466' => [0.0, '123.456, 0'],
     [1.5, '1.5, 0.1'],
     [0.23, '0.234, 0.01'],
     [123, '123.456'],
diff --git a/tests/data/Calculation/MathTrig/FLOORPRECISE.php b/tests/data/Calculation/MathTrig/FLOORPRECISE.php
index 73f13ceb2..b35a0c7c5 100644
--- a/tests/data/Calculation/MathTrig/FLOORPRECISE.php
+++ b/tests/data/Calculation/MathTrig/FLOORPRECISE.php
@@ -8,7 +8,7 @@ return [
     [-4, '-2.5, 2'],
     [2, '2.5, -2'],
     [0.0, '0.0, 1'],
-    ['#DIV/0!', '123.456, 0'],
+    'corrected with PR 4446' => [0.0, '123.456, 0'],
     [1.5, '1.5, 0.1'],
     [0.23, '0.234, 0.01'],
     [123, '123.456'],