Forum Discussion

Flopbot2's avatar
Flopbot2
Copper Contributor
Nov 06, 2024
Solved

Line Graph Should Ignore Blank Cells

Greetings, I have an Excel line graph that doesn't seem to be working properly and I'm not sure why.  It's acting more like a bug with the Excel file itself - or perhaps the Excel program - than a p...
Charting
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Nov 07, 2024

    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.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 07, 2024

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.

 

Resources