Forum Discussion

conpage's avatar
conpage
Copper Contributor
Dec 27, 2022

Need Help with Line Chart Not Recognizing Blank Cells

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,"")

 

 

Charting
excel
Formulas and Functions

2 Replies

Sort By
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Dec 27, 2022

    conpage 

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

    Like these:

     

    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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 27, 2022

    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.

Resources