One more conditional formatting issue

[Lupp]

@erAck

The + operator does not take reference lists, not even in array mode…

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.

[erAck]

@sokol92

=SMALL(A1:A10+B1:B10;2)

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.

[erAck]

@Lupp

Why isn’t a ReferenceList first converted to a NumberSequence which seems to make arithmetical expressions acceptable?

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).

[eeigor]

ArrayCondition2.ods (21.2 KB)

Updated @Villeroy example. Everything works in both Excel and Calc, but it’s best to avoid it.

[eeigor]

Conditional Formatting

1. Works both in Excel & Calc:
   AND($B6=$B$19;$C6<=AGGREGATE(15;6;amount/(color=$B$19);$C$19))
2. 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)

[kgas]

Thanks. It is always possible to get the things done using helper cells. The point is to avoid them for obvious reasons.

[erAck]

@eeigor

Bug: it works if B19 changes (color), and if C19 changes (number), then a manual update is required. Why?

No problem here, works updating also with a new Number in C19. LO 7.2.

[Lupp]

@kgas

The point is to avoid them {helper cells} for obvious reasons.

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.

[eeigor]

@kgas

It is always possible to get the things done using helper cells.

The answer is given. The formula with AGGREGATE looks a little better than SUMPRODUCT one.

@erAck

No problem here, works updating also with a new Number in C19. LO 7.2.

Yes, indeed. It didn’t work in version 7.1.5.2.

@Lupp

A well designed sheet should rarely need single cells as helpers

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.

[Lupp]

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.

[kgas]

@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.