Unable to calculate values of formulas using combined named ranges

[DigitalFeonix]

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)

What is the expected behavior?

That combining ranges (ie; firstRange:secondRange) would evaluate down to a single usable range like Excel does.

What is the current behavior?

When running getCalculatedValue() against a formula field with a single named range such as =VLOOKUP(A2,myRange,2) PhpSpreadsheet has no issue getting the calculated value.

When running getCalculatedValue() against a formula field with combination named ranges such as =VLOOKUP(A2,rateKeys:rateValues,2) PhpSpreadsheet throws an error like Example!C2 -> Invalid cell coordinate rateKeys.

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 __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$reader = new Xlsx();

$spreadsheet = $reader->load('example.xslx');
$spreadsheet->setActiveSheetIndexByName('Example');

$active_sheet = $spreadsheet->getActiveSheet();

// add code that show the issue here...
$good_cell  = $active_sheet->getCell('B2');
$good_value = $good_cell->getCalculatedValue();

$bad_cell  = $active_sheet->getCell('C2');
$bad_value = $bad_cell->getCalculatedValue();

Which versions of PhpSpreadsheet and PHP are affected?

PHP 8.0.13
PhpSpreadsheet 1.22.0

File to reproduce

example.xlsx

[MarkBaker]

I wasn't even aware that it was possible to use the : range operator with existing ranges rather than simply with cellRanges (A1:B3), row ranges (1:2) or column ranges (A:B) in Excel; how does Excel treat what amounts to A1:A204:B1:B204? Is it always compressed to effective A1:B204? If so (and that's how it appears from your evaluation screenshots).

The calculation engine expects a cell reference, row reference or column reference on each side of the : range operator. It might allow a named range if that named range evaluates to a single cell so that it can evaluate the result of that range operation as a cell range; but I'd have to test even that to confirm it.

So there isn't going to be any quick fix for this. It means a fairly significant amount of code change to the Calculation Engine; and it will need a lot of testing, particularly for edge cases (e.g. if you had ratevalues:ratekeys).

[DigitalFeonix]

From my testing inside Excel (version 2203 build 15028.20160) it seems that it evaluates to a range/box that encompases all of the ranges/cells inside. In the image below, if the yellow cell, blue cell, and orange cell range are present. It will evaluate down to the selection box.

with named ranges of:

rateKeys    =BigData!$A$2:$A$204
rateValues  =BigData!$B$2:$B$204

range evaluations

rateKeys:rateValues         => BigData!$A$2:$B$204
rateValues:rateKeys         => BigData!$A$2:$B$204
BigData!$A$2:rateValues     => BigData!$A$2:$B$204
BigData!$A$204:rateValues   => BigData!$A$2:$B$204
BigData!$B$2:rateKeys       => BigData!$A$2:$B$204
BigData!$B$204:rateKeys     => BigData!$A$2:$B$204

mixed cells/range evaluations

rateKeys:BigData!$C$1       => BigData!$A$1:$C$204
rateKeys:BigData!$C$256     => BigData!$A$2:$C$256
rateKeys:BigData!$A$256     => BigData!$A$2:$A$256

rateValues:BigData!$A$256   => BigData!$A$2:$B$256
rateValues:BigData!$A$1     => BigData!$A$1:$B$204

three (or more) cells/ranges evaluations

rateValues:BigData!$A$256:BigData!$C$1                      => BigData!$A$1:$C$256
BigData!$B$256:BigData!$C$1:BigData!$A$127:BigData!$D$512   => BigData!$A$1:$D$512
BigData!$B$512:BigData!$C$1:BigData!$A$127:BigData!$D$127   => BigData!$A$1:$D$512

So the bounding box range is $minCol$minRow:$maxCol$maxRow

[MarkBaker]

Useful to know, thanks. I'll try and do some experimenting over the weekend, or see if I can track down any Microsoft documentation that can confirm your observations. I'll also double check the union and intersection operators when using named ranges (although I'm reasonably sure that they already work as expected).
Fixing the range operator will take longer; but it is on my radar now.

[MarkBaker]

A little experimentation tells me that it isn't just named ranges either: the range operator can be chained - =SUM(B1:E4:B5:E9) or (with named ranges) =SUM(NamedRange_1:NamedRange_2:NamedRange_3).

I don't know if there's any limit to the chaining; but it will definitely be a while before I figure out how to implement it in the Calculation Engine.