Forum Discussion

joecolls's avatar
joecolls
Copper Contributor
Aug 21, 2023

Plotting blank cells

I am plotting a time series of readings.  The dates are converted from "day of the year" to "day of the heating season" by a formula.  If the date is in the future when no reading has yet been taken, it shows as a blank. 

For example

=IF(ISBLANK(A395),"",B395-213).

If the data selected for plotting is only the dates for which readings have been created, it works fine.  But if the data range is extended to include blanks calculated by the above formula, the whole graph is corrupted.  Instead of the x cordinates being the day of the heating season (eg 6, 13, 20....), they are converted to a numerical order (1, 2, 3....). 

If I manually delete the formula from the blank dates then the graph is restored.

How can I get Excel to plot a formula-created blank in the same way that it plots a manually-created blank?

 

Charting
Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 21, 2023

    joecolls 

    You might use the FILTER function to create a new range containing only the non-blank values, and use that as source range for the chart.

    • joecolls's avatar
      joecolls
      Copper Contributor
      Aug 24, 2023

      HansVogelaar 

      Thanks Hans.  That's a good suggestion as a workaround.  I was really hoping that I had missed some basic way of telling Excel to ignore the formula that creates the blank cell, and just plot the blank cell.  Maybe that doesn't exist.

      Joe

Share

Resources