Plotting Data against times - Dealing with missing values

Hi,

I have read through every post that the searches i thought might get me relevant information and not found a solution. appologies if this is one of those issues that people bring up every few days. i have had no luck finding a solution as yet.

I am trying to plot temperature data for a year on a chart. there should be one value for each day however some days are missing data. What i want to acheive is a break in the line when no data exists and not interpolation or zero values. i also need for the x axis to keep every value ie i dont want it to miss a day because it has no data.

I can get this to work perfectly if i manually delete the formula in cells that return no data. However if i make the value of the cell "" or N/A or NULL() or any other thing i think is sensible i can only get the chart to either put in zero's or draw a line between the last value and the next value.

i have played with many different iterations of settings under the chart options on how to deal with blank cells.

i have considered using seperate series for each 'block' of data, however i need this to be automatic and cant see a way to do this.

My backup option is to run some vbs on the column to copy all non zero values to a seperate column and therefore ensure true blank cells. this seems a bit of a cheat, however.

I hope this makes sense. i will attach an example if it would help. i imagine this will be a simple 'this is why you are being an idiot' type of answer though!

Cheers
Stephen

Thanks andy,

unfortunately the data is a bit to complex to use the mask, and it has other graphs behind it. I think i will have to just go with the copy and past method.

thank you very much for your help - i wish i had asked sooner!

cheers
Stephen

Hope its ok to 'highjack' my own thread.

i thought it would be simple to 'copy and paste' the values to a new column and delete the blank ones in VB. however using .delete on a cell that is empty does not result in the formula isblank() returning true. if i highlight the cell and manually press delete it evaluated correctly.

any advice on how to make a cell blank in vba?

many thanks
Stephen

Clear! thanks so much andy.

not having a smart day.

Thanks Again
Stephen