Forum Discussion
Line Graph Should Ignore Blank Cells
This failure to recognise that cells containing an empty string are to be considered as blank is a long-standing 'feature' of Excel. Riny_van_Eekelen 's solution of forcing #N/A errors is a standard for traditional spreadsheets but with dynamic arrays other solutions are possible. In particular
= FILTER(SCAN(, admissions, SUM), admissions) Insider only = SCAN(, TRIMRANGE(admissions), SUM)will both create truncated output datasets that work well with charts and create an improved tabular presentation.
Your formulas filled the blank cells with empty strings. These are not the same as empty cells. Therefore, setting empty cells as gaps doesn't work. The cells must be truly empty or contain #N/A. And why enter spaces in columns D:E for months with no data? Just leave them empty. Fixed all of it in the attached file, and changed some number formatting as well. Would that work for you?
