How to get a chart to ignore blank cells in data?

[leojez]

Hello,

I have a chart which includes blank cells for the series data of the line. I'd like the chart to ignore the blanks completely - as you can see, it currently pots those blank cells at zero:

The formula I have in the blank cells is

=IF(L17<>"",IFERROR(N17/J17,"0%"),"")

I'd like the chart only to plot data that is not blank. Any ideas please?

Thank you!

 

[Peter_SSs]

I want the chart to start at the first date when there is data. So it should look like this:

Actually, your chart starts from 0% so it is actually starting at the last date that does not have data isn't it?

Then I think that you may have to set up a different range to get the chart data for that chart and use that, with a few tricks, to get the chart.
This is how I did it

• New formula in column O (& it is deliberate that there is a reference to row 7 in the row 8 formula)
• New section with dynamic array formulas for the week ending dates and ROI2 percentages. Mine is in N23:O23 and below.
• The trick to using spill ranges via named ranges is described in this video.

Mini-Sheet

leojez.xlsm
	B							I	J	K	L	M	N	O
7	Week ending							Column2	Capital2	Close ($)2	Close2	Wkly profit2	Cum profit2	ROI2
8	22-Jul-22								30000	30000	36000	0	6000	20%
9	15-Jul-22								30000	30000	36000	0	6000	20%
10	08-Jul-22								30000	30000	36000	36000	6000	20%
11	01-Jul-22													0%
12	24-Jun-22
13	17-Jun-22
14	10-Jun-22
15	03-Jun-22
16	27-May-22
17	20-May-22
18	13-May-22
19	06-May-22
20	29-Apr-22
21	22-Apr-22
22
23													WE	ROI2
24													01-Jul-22	0%
25													08-Jul-22	20%
26													15-Jul-22	20%
27													22-Jul-22	20%
28
Sheet3

Cell Formulas
Range	Formula
M8:M21	M8	=IF(L8<>"",(L8-L9),"")
N8:N21	N8	=IF(L8<>"",(L8-J8),"")
O8:O21	O8	=IF(L8<>"",N8/J8,IF(L7<>"",0,""))
B8:B20	B8	=IFERROR((B9+7),"")
N24:N27	N24	=LET(t,FILTER(B8:B21,O8:O21<>""),rws,ROWS(t),INDEX(t,SEQUENCE(rws,,rws,-1)))
O24:O27	O24	=LET(t,FILTER(O8:O21,O8:O21<>""),rws,ROWS(t),INDEX(t,SEQUENCE(rws,,rws,-1)))
Dynamic array formulas.

Image

 

[leojez]

That works great! Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Jon Peltier]

Your problem is that you are plotting actual dates against cells which may have #N/A (upper data range and chart in image below). There is no point for these cells, but there are still dates, so the axis shows them.

But if the value in the date column and the value in the ROI column are both #N/A, then there will be no points for that data pair, no dates, and no space along the axis.

In the second data range and chart below, I've inserted another column for the dates in the specific chart. The formula in cell C14 is:
.

=IF(ISNA(D14),NA(),B14)

The second chart uses this inserted column as its X values.