Forum Discussion
Empty cells are displayed in a chart when using FILTER() function, when I don't want them to be
damienid As you have discovered, FILTER returns 0 (zero) when it encounters an empty cell. Zero is neither empty nor null. For your chart to work, you need to replace the empty cells in your table by #N/A's. Mocked up a small example to illustrate what I mean. See attached.
damienid As you have discovered, FILTER returns 0 (zero) when it encounters an empty cell. Zero is neither empty nor null. For your chart to work, you need to replace the empty cells in your table by #N/A's. Mocked up a small example to illustrate what I mean. See attached.
I would argue the FILTER does return a blank, though that is not how it appears on the worksheet. The formula
= LET( filtered, FILTER(Table1[[Ref1]:[Ref2]], Table1[Item]<>""), IF(filtered="", NA(), filtered) )discriminates between blanks and zeros in the source data.
Riny_van_Eekelen Great, thank you. I had tried to use #N/A before but had done it wrong so thanks for the example using the NA() function which looks like it has done the trick!
