Find & Replace with Matrix Formula

English
#1

I have a bunch of SUMIF statements that I need to fix.

Previously I was using this formula to ignore #NA errors:

=SUMIF([range],"<>#N/A",[range])

This no longer works in v7 because v7 no longer returns #N/A as the error I’m trying to ignore. It’s now Err:503. I suppose I could just change this to =SUMIF([range],"<>Err:503",[range]) but I’d like to use a matrix formula to filter errors more robustly, and to (hopefully) speed things up. It will also keep my documents compatible with both v6 and v7. With the help of this answer, I’ve identified this matrix formula as a more suitable replacement:

=SUM(IFERROR([range],""))

This works as long as I enter the replacement formula using Ctrl-Shift-Enter to tell Calc that the use of IFERROR should return a matrix.

So far so good. My problem is now with find & replace. I have hundreds of these formula spread across multiple sheets and documents. I’ve accepted that I have to update sheets individually, but I don’t want to go cell-by-cell. So, I’m using Find & Replace to update the formulas. These regexes correctly replace the old formula with the new one:

Find: ^=SUMIF\((.+),\"<>#N/A\".+
Replace: {=SUM(IFERROR($1,""))}

My understanding is that surrounding the formula with { } should tell calc to interpret the cell as a matrix formula (i.e. it should accomplish the same thing as entering the formula using Ctrl-Shift-Enter). However this doesn’t work. The cells contain the correct formula surrounded in curly braces, but are not recognized as a valid matrix formula.

How do I tell find & replace that the replacement is a matrix formula? Is that even possible?

#2

Such literal error strings in conditions anyway only work by chance and would stop working as soon as the document was loaded in an UI language that translated the #N/A error string differently.

And no, using Find&Replace to replace to an array/matrix formula does not work, the surrounding {} braces are only a visual indicator when viewing array formulas in cell / Input Line and not part of the formula expression.

Edit: if your expressions are only about SUM() formulas, you could use SUMPRODUCT() in which all arguments are forced into array context, with only one argument, like

Find: ^=SUMIF\((.+),\"<>#N/A\".+
Replace: =SUMPRODUCT(IFERROR($1,0))

#3

Hence, my desire to change it.

Thanks for confirming Find&Replace won’t do what I need. Can you suggest a workaround that doesn’t involve manually replacing every cell?

#4

For SUM() it should be rather simple using a replacement like:
=SUMPRODUCT(IFERROR([rangeRef];0))
This is based on the fact that the parameters of SUMPRODUCT() are specified ForceArray.
With other functions of the SUM() family it’s more complicated.
See attached tiny example.
disask99071avoidNeedOfArrayEvaluation.ods (18.6 KB)

#5

Thank you! That will work for my needs … I have to admit, I find that subtlety in behaviour between SUM and SUMPRODUCT quite confusing, but it does what I need, and it will work with find & replace.

#6

The distinction between functions working with lists and those working with arrays is a bit subtle, but is made to allow for lists where arrays forced to be equally dimensioned can’t do the task. You have a case with a single range where the reasons don’t show clearly. Think of SUM(A1:A12;B14:G18) to see the problem. If the list-basing functions are actually needed is a different question. They have their tradition, that’s it. Omitting them now would break millions of working spreadsheets.

#7

Thanks for the explanation. I definitely had no idea those two functions operated on different data types. I understand the difference between lists and arrays in the context of programming, but I didn’t realize those differences came into play in Calc. Thanks for the lesson.

#8

See Index of /office/OpenDocument/v1.3/cs01/part4-formula/ if interested in details - and how they are “officially” described…

#9

I’ve solved my immediate need, but for further information:

Searching for a matrix formula seems to indicate that the {} are more than visual, but not the same as literal {}.

Searching for a regex ^=[formula] does NOT find results contained in matrix formulas. Neither does searching for ^{=formula]. =[formula] DOES find results, which suggests that calc doesn’t see matrix formulas as starting with = or {, but some other character(s?) that is displayed at {. So, it seems that the {} are something more than visual indicators (since they are “seen” by the regex), but something less than part of the formula expression.

Incidentally, trying to replace a matrix formula with a non-matrix formula fails silently when using Find & Replace, (pressing “Replace” does nothing, and does NOT advance to the next cell). I’d guess this is probably because of the cell protection that matrix formulas grant, but failing silently is not very desireable. It feels like a bug to me.

#10

Thanks, I’ll keep that in mind next time I need formula reference. The online docs are more readable, but aren’t as well specified. I didn’t understand that distinction by reading the online docs alone.

#11

Yes it’s tricky… searching for formula text searches the representation of a formula expression, that for array formulas includes the surrounding {} braces, which are not present for normal formulas. So searching for ^=.* does not match the array formula {={1}} but searching for ^\{=.* does (note that you have to backslash escape literal { as it is a regex meta character).


However, attempting to replace a match of a non-array formula with an array formula will enter a text {={2}} literally, not as formula, as if entered by keyboard input. But, while a single-cell array formula ike {={1}} can be matched with ^\{=.* it can not be replaced with {={2}} in a single Replace (which looks like a bug to me), but it can be replaced with Replaced All, but only to an array formula, so a replacement with {={2}} will yield that, but a replacement with =2 will yield {=2}. If that wasn’t done then array formulas would be destroyed when replaced.


And yes, multi-cell array formulas can not be replaced at all because the replacement is attempted on single cells, which is vetoed for a multi-cell array.

#12

Oh, of course! I missed escaping the {. Thanks for spelling it out.

I’m still confused about going the other direction: Replacing a regular formula with an array formula … if the {} are literal, how do you create an array formula? Is that the bug you’ve identified?

#13

No, what I considered to be a bug is that replacing an array formula with an array formula does not work in a single Replace but does work in Replace All.
Not being able to replace a non-array formula with an array formula would be a related but other bug/RFE. What’s probably (didn’t inspect) happening there is that only in the case of an existing array formula the replacement surrounding {} braces are taken as “this is meant to be an array formula as well”.
Would help if someone tried out the actual cases and combinations in detail and submitted a bug.

#14

I confirmed your finding that “replace all” works and the single-instance “replace” does not, and added a comment to the bug you referenced.

I also added two new bugs:
UI: Find & Replace does not create array formulas
UI: Ctrl-Shift-Enter does not create array formula if cell is unmodified

Hopefully that is enough info for the developers.

And, in the meantime, I’m still using the non-array-formula posted by Lupp in post #4.

1 Like
#15

=AGGREGAT(9;2;A$1:A$20)

9 : Sum
2 : ignore any error

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