As far as I know, the xlsx format does not allow you to store the formula for a conditional formatting rule as an array formula. Also, when defining a conditional formatting rule, you cannot specify an array formula interactively (Ctrl + Shift + Enter) or through the properties of the FormatCondition object.
So Excel has (as usual) its own opinion whether you entered an array formula or not.
It may not be a matter of array formula, hence my question. Some functions force some arguments to array mode, like SUMPRODUCT() does. We may have missed to implement that for the first parameter of SMALL() and LARGE().
The first parameters of SMALL() and LARGE() are specified NumberSequencList, surely to be designated as a ReferenceList in a use-case.
Can this be compatible with ForceArray? The specifications don’t contain the ForceArray.
My previous post was solely about conditional formatting formulas.
If in Excel (2019, en_US), in the conditions of the example from @Villeroy, we enter in any cell the array formula
=SMALL(IF(color=$E$5,amount),3)
then the result will be 300. If you enter as a regular formula, then #NUM!
@erAck Yes, you are correct. In a single cell without CSE this will produce error. But in the conditions formula it returns the values correctly. I miss read your earlier post.
It could in this case as all references or array elements can be converted/appended to one numeric vector which the function expects.
However, according to
that’s not what Excel does.
Does this apply to (e.g.) {=SMALL(A1:A10~B1:B5+E1:E10~D6:D10;2)}?
If implemented, would it work with any two ReferenceList operands returning the same number of numeric list elements?
(That’s not fully realistic, but the software must do something even in less realistic cases. It can’t just grunt angrily.)
The + operator does not take reference lists, not even in array mode 
Not sure what you’re up to. SMALL() and LARGE() take reference lists of any different dimensions, be it in normal or in array mode.
I don’t understand.
Array formula
=SMALL(A1:A10+B1:B10;2)
works in Calc (and in Excel). For arrays of different sizes it returns #VALUE!
This far I knew, but not about the “can be” I quoted in my previous comment. .
Under array evaluation SMALL() and LARGE() also accept differently dimensioned ranges combined by an arithmetic operator as long as they have the same total number of elements.
I didn’t feel sure how this is covered by the NumberSequenceList specification, and how an additional ForceArray would apply. Why isn’t a ReferenceList first converted to a NumberSequence which seems to make arithmetical expressions acceptable?
I would also accept to put an “over” here and end this long discussion. I’m probably not capable of understanding some of the details.
That is not a reference list but two range reference operands of operator + and does not pass a reference list to SMALL() but the expression A1:A10+B1:B10 is evaluated first and its array result (one column vector of 10 rows here) is passed to SMALL(). Operator + does not work with operands of cell ranges of different sizes.
Because it’s undefined in the context of an operator that expects operands of identical dimensions and sizes. You could reason that multiple vectors of single columns or rows each could be a special case that simply could be concatenated in the order encountered, but spreadsheet implementations just don’t do it (AFAIK).
ArrayCondition2.ods (21.2 KB)
Updated @Villeroy example. Everything works in both Excel and Calc, but it’s best to avoid it.
Conditional Formatting
- Works both in Excel & Calc:
AND($B6=$B$19;$C6<=AGGREGATE(15;6;amount/(color=$B$19);$C$19)) - Define a name (referring to the cell with an array formula as below):
HelperCell: {=SMALL(IF(color=$B$19;amount);$C$19)}
AND($B6=$B$19;$C6<=HelperCell)
Bug: it works if B19 changes (color), and if C19 changes (number), then a manual update is required. Why?
The file with the example has been updated.
ArrayCondition2.ods (49.1 KB)
Thanks. It is always possible to get the things done using helper cells. The point is to avoid them for obvious reasons.
No problem here, works updating also with a new Number in C19. LO 7.2.
A well designed sheet should rarely need single cells as helpers, but mostly columns which can easily be hidden. Considering the advantages of smpler formulas and the ease of maintenance they often come with I feel a bit lost concerning the obvious reasons.
1 Like
The answer is given. The formula with AGGREGATE looks a little better than SUMPRODUCT one.
Yes, indeed. It didn’t work in version 7.1.5.2.
Notice that the array calculation in the formula calculates the same value in each cell. This is a lot of repeat calculating. The volatile nature of conditional formatting compounds the problem. To get around this repeat calculating, it would be beneficial if you used a helper cell.
Not sure about how to understand the comment concerning CF here.
Only want to state that evaluations looking rather inefficient (needing to search through arrays, e.g.) are less problematic if used for CF conditions because they only are executed for cells getting anew into the viewed range of the sheet.
@Lupp . This is not about CF but on the helper cells/columns.There are users, who unhide and delete the cells/columns and complains. Normally in excel what I used to do is protect the sheets, the user is given an option to open a form to work with. once the form is open it will unprotect the sheet and user is not allowed to move out of the form. Once the form is closed again protect the sheets. Such long procedure can be avoided without helper cells. sorry for the long answer.