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]

The formula I have in the blank cells is

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

Try this formula instead

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

 

[leojez]

Hi, thanks so much for helping! I've applied your formula and this is what I get:

The line has been removed but the chart doesn't resize to only show the data - there's a large blank. The X axis is dates, so the #N/A cells are still registering those dates. Additionally, I'd like the cells to show blanks instead of #N/A. Possible?

 

[Kemidan2014]

This is a topic i am interested in, i just tried the NA() trick and i can tell you it does not work. it did what you just posted as i was typing. I think the issue is that what your wanting your graph to do is have the range of data be dynamic.

I have a graph where i am counting occurences of types of things in a parato graph, and half the categories are zero but I am aware over time that could change with time. I think ultimately we might be resigned to either leaving it as is with zero values OR having to tailor the data selection each time you want to show it.

But i have hope theres a solution out there!

 

[Peter_SSs]

@Kemidan2014
Please start your own thread rather than hijack a currently active one. You can put a link in your thread to this one if you think it relevant.

The line has been removed

.. which is all that you actually asked for.

I'd like the chart only to plot data that is not blank.

To offer further suggestions, it would be good to have some small sample data to work with as we can do nothing with a picture and have no idea what columns and rows the data shown is in and therefore are guessing what the formulas are referring to. See the XL2BB link in my signature block below.

 

[leojez]

Hi, thanks for your help so far. Below (hopefully, if I've done it correctly) is the mini sheet. Unfortunately, it doesn't seem to include the charts, so I'll attach as screenshot as well. As you can see, the ACCOUNT #2 chart includes all the dates from the beginning of the table. However, I'd like the Account #2 chart to start plotting from the first ROI number - in this example 08 Jul 2022. Is there a way to do this?

Thank you!

Portfolio Balance Tool sample data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P								X	Y	Z	AA	AB	AC
1
2			ACCOUNT #1							ACCOUNT #2														TOTALS
3
4
5
6				END OF WEEK UNREALIZED POSITIONS							END OF WEEK UNREALIZED POSITIONS														TOTAL UNREALIZED POSITIONS
7		Week ending	Capital	Close ($)	Close	Wkly profit	Cum profit	ROI	Column2	Capital2	Close ($)2	Close2	Wkly profit2	Cum profit2	ROI2	Column3								CAPITAL7	POSITION7	CUM PROFIT7	ROI7	Column1	Week ending2
8		22 Jul 2022	30,000 €	$30,000	36,000 €	1,000 €	6,000 €	20%		30,000 €	$30,000	36,000 €	0 €	6,000 €	20%									60,000 €	72,000 €	12,000 €	20%		22 Jul 2022
9		15 Jul 2022	30,000 €	$30,000	35,000 €	3,000 €	5,000 €	17%		30,000 €	$30,000	36,000 €	0 €	6,000 €	20%									60,000 €	71,000 €	11,000 €	18%		15 Jul 2022
10		08 Jul 2022	30,000 €	$30,000	32,000 €	-1,500 €	2,000 €	7%		30,000 €	$30,000	36,000 €	36,000 €	6,000 €	20%									60,000 €	68,000 €	8,000 €	13%		08 Jul 2022
11		01 Jul 2022	30,000 €	$30,000	33,500 €	0 €	3,500 €	12%																30,000 €	33,500 €	3,500 €	12%		01 Jul 2022
12		24 Jun 2022	30,000 €	$30,000	33,500 €	0 €	3,500 €	12%																30,000 €	33,500 €	3,500 €	12%		24 Jun 2022
13		17 Jun 2022	30,000 €	$30,000	33,500 €	200 €	3,500 €	12%																30,000 €	33,500 €	3,500 €	12%		17 Jun 2022
14		10 Jun 2022	30,000 €	$30,000	33,300 €	100 €	3,300 €	11%																30,000 €	33,300 €	3,300 €	11%		10 Jun 2022
15		03 Jun 2022	30,000 €	$22,000	33,200 €	200 €	3,200 €	11%																30,000 €	33,200 €	3,200 €	11%		03 Jun 2022
16		27 May 2022	30,000 €	$22,000	33,000 €	500 €	3,000 €	10%																30,000 €	33,000 €	3,000 €	10%		27 May 2022
17		20 May 2022	30,000 €	$20,365	32,500 €	700 €	2,500 €	8%																30,000 €	32,500 €	2,500 €	8%		20 May 2022
18		13 May 2022	30,000 €	$20,425	31,800 €	300 €	1,800 €	6%																30,000 €	31,800 €	1,800 €	6%		13 May 2022
19		06 May 2022	30,000 €	$20,874	31,500 €	500 €	1,500 €	5%																30,000 €	31,500 €	1,500 €	5%		06 May 2022
20		29 Apr 2022	30,000 €	$20,947	31,000 €	1,000 €	1,000 €	3%																30,000 €	31,000 €	1,000 €	3%		29 Apr 2022
21		22 Apr 2022	30,000 €	$34,957	30,000 €	30,000 €	0 €	0%																30,000 €	30,000 €	0 €	0%		22 Apr 2022
22
WEEKLY POSITION

Cell Formulas
Range	Formula
F8:F21,M8:M21	F8	=IF(E8<>"",(E8-E9),"")
G8:G21,N8:N21	G8	=IF(E8<>"",(E8-C8),"")
H8:H21,O8:O21	H8	=IF(E8<>"",IFERROR(G8/C8,"0%"),"")
X8:X21	X8	=C8+J8
Y8:Y21	Y8	=E8+L8
Z8:Z21	Z8	=Y8-X8
AA8:AA21	AA8	=IFERROR(Z8/X8,"0%")
C9:C12	C9	=C10
B8:B20	B8	=IFERROR((B9+7),"")
AC8:AC21	AC8	=B8

 

[Peter_SSs]

Try changing the column O formula as shown below.

I have used Conditional Formatting to hide the #N/A error values - by making the font the same colour as the background.

leojez.xlsm
	J	K	L	M	N	O
1
2	ACCOUNT #2
3
4
5
6		END OF WEEK UNREALIZED POSITIONS
7	Capital2	Close ($)2	Close2	Wkly profit2	Cum profit2	ROI2
8	30000	30000	36000	0	6000	20%
9	30000	30000	36000	0	6000	20%
10	30000	30000	36000	36000	6000	20%
11
12						#N/A
13						#N/A
14						#N/A
15						#N/A
16						#N/A
17						#N/A
18						#N/A
19						#N/A
20						#N/A
21						#N/A
Sheet1

Cell Formulas
Range	Formula
M8:M21	M8	=IF(L8<>"",(L8-L9),"")
N8:N21	N8	=IF(L8<>"",(L8-J8),"")
O8:O21	O8	=IF(L8<>"",IFERROR(N8/J8,"0%"),IF(L7<>"","",NA()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O8:O21	Expression	=ISNA(O8)	text	NO

 

[leojez]

Thanks so much Peter. Unfortunately, it doesn't work. I think the formula you gave has the wrong cell number in the 2nd part:

=IF(L8<>"",IFERROR(N8/J8,"0%"),IF(L7<>"","",NA()))

Should it be:

=IF(L8<>"",IFERROR(N8/J8,"0%"),IF(L8<>"","",NA()))

Even so, after trying both, neither work - mini sheet and screen shot attached. Any further suggestions? Thank you!

Portfolio Balance Tool sample data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P								X	Y	Z	AA	AB	AC
1
2			ACCOUNT #1							ACCOUNT #2														TOTALS
3
4
5
6				END OF WEEK UNREALIZED POSITIONS							END OF WEEK UNREALIZED POSITIONS														TOTAL UNREALIZED POSITIONS
7		Week ending	Capital	Close ($)	Close	Wkly profit	Cum profit	ROI	Column2	Capital2	Close ($)2	Close2	Wkly profit2	Cum profit2	ROI2	Column3								CAPITAL7	POSITION7	CUM PROFIT7	ROI7	Column1	Week ending2
8		22 Jul 2022	30,000 €	$30,000	36,000 €	1,000 €	6,000 €	20%		30,000 €	$30,000	36,000 €	0 €	6,000 €	20%									60,000 €	72,000 €	12,000 €	20%		22 Jul 2022
9		15 Jul 2022	30,000 €	$30,000	35,000 €	3,000 €	5,000 €	17%		30,000 €	$30,000	36,000 €	0 €	6,000 €	20%									60,000 €	71,000 €	11,000 €	18%		15 Jul 2022
10		08 Jul 2022	30,000 €	$30,000	32,000 €	-1,500 €	2,000 €	7%		30,000 €	$30,000	36,000 €	36,000 €	6,000 €	20%									60,000 €	68,000 €	8,000 €	13%		08 Jul 2022
11		01 Jul 2022	30,000 €	$30,000	33,500 €	0 €	3,500 €	12%							#N/A									30,000 €	33,500 €	3,500 €	12%		01 Jul 2022
12		24 Jun 2022	30,000 €	$30,000	33,500 €	0 €	3,500 €	12%							#N/A									30,000 €	33,500 €	3,500 €	12%		24 Jun 2022
13		17 Jun 2022	30,000 €	$30,000	33,500 €	200 €	3,500 €	12%							#N/A									30,000 €	33,500 €	3,500 €	12%		17 Jun 2022
14		10 Jun 2022	30,000 €	$30,000	33,300 €	100 €	3,300 €	11%							#N/A									30,000 €	33,300 €	3,300 €	11%		10 Jun 2022
15		03 Jun 2022	30,000 €	$22,000	33,200 €	200 €	3,200 €	11%							#N/A									30,000 €	33,200 €	3,200 €	11%		03 Jun 2022
16		27 May 2022	30,000 €	$22,000	33,000 €	500 €	3,000 €	10%							#N/A									30,000 €	33,000 €	3,000 €	10%		27 May 2022
17		20 May 2022	30,000 €	$20,365	32,500 €	700 €	2,500 €	8%							#N/A									30,000 €	32,500 €	2,500 €	8%		20 May 2022
18		13 May 2022	30,000 €	$20,425	31,800 €	300 €	1,800 €	6%							#N/A									30,000 €	31,800 €	1,800 €	6%		13 May 2022
19		06 May 2022	30,000 €	$20,874	31,500 €	500 €	1,500 €	5%							#N/A									30,000 €	31,500 €	1,500 €	5%		06 May 2022
20		29 Apr 2022	30,000 €	$20,947	31,000 €	1,000 €	1,000 €	3%							#N/A									30,000 €	31,000 €	1,000 €	3%		29 Apr 2022
21		22 Apr 2022	30,000 €	$34,957	30,000 €	30,000 €	0 €	0%							#N/A									30,000 €	30,000 €	0 €	0%		22 Apr 2022
22
WEEKLY POSITION

Cell Formulas
Range	Formula
F8:F21,M8:M21	F8	=IF(E8<>"",(E8-E9),"")
G8:G21,N8:N21	G8	=IF(E8<>"",(E8-C8),"")
H8:H21	H8	=IF(E8<>"",IFERROR(G8/C8,"0%"),"")
O8:O21	O8	=IF(L8<>"",IFERROR(N8/J8,"0%"),IF(L8<>"","",NA()))
X8:X21	X8	=C8+J8
Y8:Y21	Y8	=E8+L8
Z8:Z21	Z8	=Y8-X8
AA8:AA21	AA8	=IFERROR(Z8/X8,"0%")
C9:C12	C9	=C10
B8:B20	B8	=IFERROR((B9+7),"")
AC8:AC21	AC8	=B8

 

[Peter_SSs]

Your requested result:

My result:

What aspect of my result is not in keeping with what you asked?

 

[leojez]

Hi Peter, sorry if I wasn't clear. I want the chart to start at the first date when there is data. So it should look like this: