STYLE function applied only to first cell in array formula

English
#1

I have a spreadsheet that makes considerable use of Named Expressions (as in Ctrl+F3). It would be very nice if these could flag themselves by color-filling the cells they are used in with …&T(STYLE(…)). In fact, this works perfectly when the Named Expressions are used as single-cell entries, and it seems to be performant. However, when STYLE() is used within an array formula, only the top left cell reflects the style. This is sad, because it would be nice to see the array formula region that is populated with a Named Expression even if the cells have blank results.

Notice, that I am just giving the “compelling reason” to use STYLE() as stipulated from the STYLE() docs. The behavior is in no way dependent on Named Expressions vs. direct use of STYLE() in an array formula.

Of course, as a one-off, I can always hand fill the same region. Of course, I can guess at a larger region and come up with some sort of conditiional formatting to flag with. But this would easily lead to desynchronizing filled regions and “clipping” data that should be there or having “dead” data appended from previous uses without an automatic one-to-one flagging of the cells, since, let’s face it, LO’s array formula auto-sizing isn’t exactly as nimble as, say, Excel’s auto-spill.

Edit: Uploading example. The behavior seems to be direct and consistent. Here is an example:

ArrayFormulaWithSTYLE.ods (7.4 KB)

Steps to reproduce:

  1. Select (highlight) multiple cells.
  2. In the formula bar enter something like =“Hello”&T(STYLE(“Accent”)) pressing Ctrl+Shift+Enter to complete.

Observed behavior:
Calc will only format the top left cell as Accent, but enter Hello into each selected cell.

Expected behavior:
Calc would format all the selected cells as Accent, and enter Hello into each selected cell.

Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 20; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

#2

LibreOffice version?

Please upload a small sample file here.

#5

Hi @joshua4, alternatively, select the matrix formula area, press F11, under Styles, locate the matrix formula and click on it.

Remembering the Matrix Formula area disables the inclusion and exclusion of rows and columns.

#3

The solution is simple: do not use the STYLE() function, use conditional formatting instead.

The underlying reason in this case here is that while the array formula is evaluated, the results are stored in an array/matrix that gets attached to the top left cell, and all other array formula’s cells reference that cell with offsets into the matrix to obtain their values to be displayed. The STYLE() side effect of modifying the cell attributes isn’t even executed for those.

3 Likes
#4

@erAck, the fact that array formulas work internally as offsets from the top left cell is handy information, as a way to picture array formulas that may be more realistic than my go-to mental map, which has been that of a “window” the size of the array formula.

But I’m going to object to listing this as an answer. Wouldn’t this be better as a comment? Saying “don’t” is not an answer, especially when the only further suggestion given was already outlined in the question itself.

1 Like
#6

I wouldn’t discuss the term “answer”, which mostly is wrongly used here as a synonym to “solution”. A relevant information, on the other hand, isn’t just a “comment”.
Let’s leave terminology aside now.
What about a workaround?
If the functionality is urgently needed, and CF isn’t a satisfying solution, you may need to create a helper sheet with two equally sized ranges for the output of

  • The actual results
  • The respective CellStyle names

Then the range where you want to display the results using the respective CellStyle can pull in the results per cell and apply the associated CellStyle using STYLE(), the side-effect-function which anyway is a breach of concept.

BTW: I will never use this workaround.

#7

[True on terminology. Call it answer, information, or comment, if it’s from the likes of @erAck it’ll probably be valuable.]

Inventive solution–two-stage formatting–probably true that it is too much overhead.

#8

I think in this case it is, because using STYLE() this way in an array formula will not work as expected. Ever.

#9

“Can’t”? :slight_smile: Thanks again for the insight about seeing array formula results as offsets.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.