Need Help with Line Chart Not Recognizing Blank Cells

Occasional Visitor

I need my charts to not drop down to zero whenever they read a blank cell and I am unsure how to do this. I tried to right click the graph and "gaps" is already selected. I think my sheet is recognizing my "blank" cells as 0 instead of blank. The formula I have for my blank cells is =IFERROR(G11/G12,"")

 

 

2 Replies

@conpage A cell with "" is not considered empty. You can use:

 

=IFERROR(G11/G12,NA())

 

It will return an #N/A! error that will be taken as a gap in the line.

Screenshot 2022-12-27 at 07.24.21.png

@conpage 

Hey, you can use an array formula to sort/remove the gaps between cells for your graph.

Like these:

sample.JPG

 

Formula:
=IF(ROWS(D$1:D1)>COUNTA(G:G),"",INDEX(G:G,SMALL(IF(G$3:G$13<>"",ROW(G$3:G$13)),ROWS(D$1:D1))))
then fill the formula below, by dragging outside selection to extend the series of formula.