Greetings, I need a help to figure out what is the issue with the conditional formatting. The source is this web site. For Blue it works but if you change to Red it fails(Calc version 7.1.4.2). It works fine with Excel
There’s no spreadsheet attachment on that site, that would show what you had put there. Hence, it’s difficult to see if there were typos/errors in your formulas… 
1 Like
It works fine with Excel
You can help us understand you better.
ArrayCondition.ods (15.1 KB)
The formula from the Excel site is a single-cell array formula. Calc can not handle array formulas in conditional formatting. But you can apply the formula to a cell range and use that for the c.f.
Does that formula in Excel work in a single cell without being entered as an array formula with Shift+Ctrl+Enter? If so it’s rather an indicator that SMALL() forces its argument to an array than an array formula working in a conditional format in Excel but not in Calc.
Well, in this case, with @Villeroy sample, using SUMPRODUCT() to force the array, seems to work fine with LibreOffice and excel.
AND($B5=$E$5;$C5<=SUMPRODUCT(SMALL(IF((color=$E$5);amount;"");3)))
That strengthens my assumption about SMALL() (and likely LARGE()) forcing array mode on their first argument, just like SUMPRODUCT() does on its arguments.
In fact, the conditional formula is open the same on excel, but with that formula in a cell, excel adds a ‘@’ on the first argument of SMALL
=AND($B5=$E$5;$C5<=SUMPRODUCT(SMALL(IF((@color=$E$5);amount;"");3)))
ArrayCondition.xlsx (9.0 KB)
The @ there is a UI representation of current row of the range named color, i.e. the explicit notation of the implicit intersection of the (cell/conditional) formula’s position and the color range. It works with and without @ (or should).
@Villeroy Thanks. Sumproduct is a neat solution. I am not sure how this work correctly in this Tutorial.
mikekaganski : Being the first post, I could not attach the calc sheet.
@erAck . yes,in excel this works without CSE. I tried index to return the array and not get the correct result.
Note: Forum has lot of checks even I can not refer more than two with @ 
@guilhem: another case of useless limitations. Limiting new users from attaching files on a resource dedicated to helping (new) users is unreasonable. I understand why mass-referencing @ is limited here, though - that latter thing should not be a blocker.
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!