Missing data for charts

[Fik]

Hello

What I am trying to ultimately achieve is a chart similar to this one where the red series is the value in column F*, and the blue one is from column A*

* http://ciul.org/stuff/chart2.png

I need both series (from A and F) to appear in the same place on the X axis, despite the fact that as you can see, the column with the date/time on the left (which refers to the values of column A) doesn't have as many date/time values as the one on the right (which refers to the values from column F).

I would like to simply not see a value for column A, when there isn't one for column A, but there is one for column F (which is more regular)
Something like this

I think I need to sort this data somehow, but I'm not even sure how to search for it.


I would appreciate your help

Thank you,
Fil

[Pauleyb]

Excel should do what you want, once you align your data to common dates. Assuming Column B is a true subset of Column E, then use vlookup to find the matching value from Column A to Column E. To ease in chart creation, you may need to create a new sheet.

Once you have the data, creating a line chart should create the chart you want. Attached is a demo.

Pauley

[Fik]

Hi
Thank you for the reply.

Is there a way I could get an empty cell instead of a #N/A if a match isn't found? #N/As don't break the series line

[Jon_Peltier]

#N/As don't break the line. Any text you might have in the cell, including "" which looks blank but isn't, cause Excel to plot a zero.

The only thing that produces a gap is an actual blank cell. You get that by deleting the contents of the cell. You can also retain the gap by formatting that segment of the series line not to have a line. Both of these are manual and need to be undone if the data changes and the blanks are in different positions.