How to deal with #N/A caused by FILTER() on a selected range?

English
, , ,
#1

Do I really need to delete and re-enter the formula every time the data changes?

In the file, under the Reference column, what if one the cells was changed not catering to the criteria (>5) anymore? The Filtered column now has #N/A. Is there a way to solve this?

Version: 24.8.3.2 (X86_64) / LibreOffice Community
Build ID: 480(Build:2)
CPU threads: 2; OS: Linux 6.6; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
24.8.3-1
Calc: threaded

Here’s the file
filter.xlsx (6.6 KB)

#2

There are a couple of bugs in relation with the matter.
Bug 127808 - Add dynamic arrays in to Calc
Bug 161642 - output range of SORT function does not expand when source range becomes larger
·
Some discussion in this thread
(Ask) New function FILTER

1 Like
#3

After knowing it’s a known bug, since I only need to not show the #N/A, I’m guessing the best bet would be hiding it with conditional formatting?

Nvm, conditional formatting doesn’t work as well. Setting Font color transparency to 100% resets to 0% after re-opening the file.

I can’t even delete a custom style that I’ve created. :person_facepalming:

Cells should not be using the custom style that needs to be deleted

#4

The mentioned bugs aren’t addressing exactly this question, but the much more general problems concerning formulas locking an output range which may suffer from underflow or overflow (spilling). The erroneous behaviour in case of exactly one needed row (or column) in a larger locked range is related to these problems.

(I’m still interested in an exchange concerning the means I proposed in the thread alraedy linked by @mariosv.)

#5

Or perhaps IFNA() :
https://wiki.documentfoundation.org/Documentation/Calc_Functions/IFNA

#6

I also tried it with IFERROR(), both didn’t work, unfortunately :frowning:

2025-01-11-195404_458x265_scrot

Font transparency resetting back to 0%
#7

Have you tried without {} (no array formula) ?

#8

It works when it’s not an array. But I want it to be an array, I want to use FILTER()

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