How to skip blank dates in scatter chart

[saitken]

Hello

I have a column of dates to plot against percentage on one sheet.
The data is copy/paste/link from another sheet.
Some of the rows in the original data have no date and the length of the column with dates will change as I add dates.
The dates in the original sheet which are blank come over as '00 January 1900' when I copy/paste/link into the sheet to create the chart.

How can I create a scatter chart which misses out the blank dates (including blanks within the dataset and at the end of it?

I think a dynamic chart does this but not sure how to skip blanks within the data set as well as after.

My chart below (I have changed the x-axis scale to cover the range of dates)

Any help would be much appreciated.

Thank you

 

[awoohaw]

try Using the NA() function for your date values:

=NA()

 

[saitken]

Thanks. But on a scatter line chart, that leaves gaps in the line.
The blanks need to be eliminated from the dataset so as to have a continuous set of dates for charts to work in Excel.
I can simply filter or sort the dates to remove the blanks but I was hoping to automate this as I have a number of rows of data which changes regularly - the above was just an example.

I found a way of doing it using by adding another column and the formula =IFERROR(INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),"",ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))),"") . This creates a new list with blanks removed. But as my data is copied and linked from another list of dates, Excel didn't recognise the linked data as blanks unless I manually deleted the linked formula in each cell with blanks. I tried making the blank cells in the original data as ="", so they are truly blank, but to no avail. It seems a cell with a linked formula in it can never be considered as a blank by Excel!

Perhaps the above formula can be changed so it looks for the blanks which are '00-Jan-00' by default. But I don't know how.

I'd like to be able to scatter plot the data in col C without having to copy the data into col D and doing a manual sort. I suppose a Macro could do it? I'm not good on Macros!

 

[awoohaw]

Okay, my suggestion would be to not use a scatter graph but a line chart.
With a secondary column to fill your dates for the NA values:

Mr excel questions 53.xlsm
	A	B	C	D
1	%	Date	% 2
2	5%	2023-04-19	#N/A
3	10%	2023-04-19	#N/A
4	14%	2023-04-27	#N/A
5	19%	2023-06-13	#N/A
6	24%	2023-06-19	#N/A
7	29%	2023-06-21	#N/A
8	33%	2023-06-21	#N/A
9	38%	2023-06-21	#N/A
10	43%	2023-06-21	#N/A
11	48%	2023-06-27	#N/A
12	52%	#N/A	2023-06-27
13	57%	2023-07-13	#N/A
14	62%	#N/A	2023-07-13
15	67%	2023-07-10	#N/A
16	71%	2023-07-10	#N/A
17	76%	2023-07-10	#N/A
18	81%	2023-07-10	#N/A
19	86%	2023-07-10	#N/A
20	90%	2023-07-10	#N/A
21	95%	2023-07-14	#N/A
22	100%	2023-07-14	#N/A
23	100%	#N/A	2023-07-14
24	100%	#N/A	2023-07-14
25	100%	#N/A	2023-07-14
26	100%	#N/A	2023-07-14
27	100%	#N/A	2023-07-14
saitken

Cell Formulas
Range	Formula
B12,B23:B27,B14	B12	=NA()
C2:C27	C2	=IF(ISNA(B2),IF(ISNA(B1),C1,B1),NA())

 

[saitken]

Ok thanks but....if 7 of the dates do not exist my percentage y-axis values needs to reflect this and so the total would be less than 100%, as in my example graph above which shows the actual 57%.
But thanks for your help. So the only solution to do this would be a way of sorting the dates oldest to newest and have them as continuous column of dates. As I say, this is easy to do manually using a filter or sort. But I am wanting automate it.

In the data below, the dates in col C are linked from another sheet and so blanks are '00-Jan-00'. I want to automate the graph creation from the % and dates data. I can copy the data and filter to get col D, then copy-fill the % so they are correct and then adjust the data range to stop at row 14 in the chart but this is a pain every time!