Yes; attached.

As noted above, I think the main issue is that plotting only offers an option for "empty cells as gaps", and after splitting the available text data I have blank or NA cells which are not shown as gaps, and don't know how to have an expression return an empty cell. So all of the attempts below fail.

Basic idea;


Tab "Data" is raw data, downloaded form web site.
tab "Weather" then copies that (Cols A:C) except reversed order.
(to get dates forward order.)

Weather!C20:C234 is the copied data of interest.
It can be:
D nn (Direction, value)
D nn G nn (Direction, value, G, value)
Calm
then cols D:F are the split out values, which are plotted.

I left in a few experiments, trying to adjust the text values from the trim(....) expression.

One was to value() it (fails for blank), another to test for "" and NA() if found,
Another to add 0 to the text value to make it numeric (which fails if blank), then adding iferror() to adjust for those failures (but still needed to return something, and can't make an empty cell).

Attached Files

weatherData.xlsm (71.2 KB, 9 views)

I don't know if this is of any help, but I have no other idea.

Attached Files

weatherData_2.xlsm (67.5 KB, 10 views)

Thanks.

I think my analysis is correct, excel formulae cannot return empty (Hello MSoft, please add an empty() function!), so one has to use VBA.

I guess there is actually some logic to this, in that making the cell empty, would also erase the formula!

One solution is to have a second copied version of the data, but only copy if blank, and clear the copy destinations first (requires a macro or VBA). What a messy situation for a simple goal!

A better solution is for MSoft to allow for other missing values in graphs.