Graph Plotting Question

[JamieP89]

Hi all,

I'm facing an issue that I've not been able to resolve by looking online myself (I suspect because the cell in question has a formula in it!)

The below set of data I am trying to plot including all months (even where blank) for ease and to not have to change the chart every time...

The only issue is want the plot to not drop dramatically when there is "0" data in the range.

I've tried and failed to use IF and IFS and also the method that includes trying to change the cell to NA through =IF(A1=0,NA()) (apologies if not exactly right.

In summary all I would like to do is take column C and ensure that when plotted with other data from elsewhere (which is where the formula is pulling the data) that the graph doesnt dip.

Hope this makes sense?

THANKS IN ADVANCE

Training Database.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Column 0	Column 1	Column 2	Column 3
2	RM	268	69	199
3	RN	12	0	12
4	NN	13	0	13
5	MSW	73	2	71
6	Total	366	71	295
7
8
9	Completed Full Training	Fill in this table
10
11	Month	RM	RN	NN	MSW	Sum
12	Nov-21	3	1	0	8	12
13	Dec-21	1	2	0	12	15
14	Jan-22	0	0	1	2	3
15	Feb-22	1	2	2	4	9
16	Mar-22	2	1	6	4	13
17	Apr-22	7	0	0	5	12
18	May-22	11	0	0	3	14
19	Jun-22	6	1	1	15	23
20	Jul-22	4	1	0	3	8
21	Aug-22	0	0	0	0	0
22	Sep-22	12	1	0	3	16
23	Oct-22	10	2	1	5	18
24	Nov-22	6	0	0	3	9
25	Dec-22
26	Jan-23
27	Feb-23
28	Mar-23
29	Apr-23
30	May-23
31	Jun-23
32	Jul-23
33	Aug-23
34	Sep-23
35	Oct-23
36	Nov-23
37	Dec-23
38
39
40
41
42
43	Month	Total RM + RN	Trained RM + RN	%	Total NN + MSW	Trained NN + MSW	%2	Total	Trained	%3
44	Nov-21	280	73	26%	86	10	12%	366	83	23%
45	Dec-21	280	76	27%	86	22	26%	366	98	27%
46	Jan-22	280	76	27%	86	25	29%	366	101	28%
47	Feb-22	280	79	28%	86	31	36%	366	110	30%
48	Mar-22	280	82	29%	86	41	48%	366	123	34%
49	Apr-22	280	89	32%	86	46	53%	366	135	37%
50	May-22	280	100	36%	86	49	57%	366	149	41%
51	Jun-22	280	107	38%	86	65	76%	366	172	47%
52	Jul-22	280	112	40%	86	68	79%	366	180	49%
53	Aug-22	280	112	40%	86	68	79%	366	180	49%
54	Sep-22	280	125	45%	86	71	83%	366	196	54%
55	Oct-22	280	137	49%	86	77	90%	366	214	58%
56	Nov-22	280	143	51%	86	80	93%	366	223	61%
57	Dec-22	280			86			366
2022

Cell Formulas
Range	Formula
D2:D5	D2	=SUM([@[Column 1]]-[@[Column 2]])
B6	B6	=SUBTOTAL(109,[Column 1])
C6	C6	=SUBTOTAL(109,[Column 2])
D6	D6	=SUBTOTAL(109,[Column 3])
F12:F37	F12	=IF(COUNT(Table8[@[RM]:[MSW]])=0,"",SUM(Table8[@[RM]:[MSW]]))
B44:B57	B44	=SUM($B$2:$B$3)
C44	C44	=IF(COUNT(B12:C12)=0,"",SUM($C$2:$C$3,B12:$C$12))
D44:D57	D44	=IFERROR([@[Trained RM + RN ]]/[@[Total RM + RN]],"")
E44:E57	E44	=SUM($B$4:$B$5)
F44:F57	F44	=IF(COUNT(D12:E12)=0,"",SUM($C$4:$C$5,$D$12:E12))
G44:G57	G44	=IFERROR([@[Trained NN + MSW ]]/[@[Total NN + MSW ]],"")
H44:H57	H44	=SUM(Table1[Column 1])
I44:I57	I44	=IF(COUNT(B12:E12)=0,"",SUM(Table1[Column 2],$B$12:E12))
J44:J57	J44	=IFERROR([@[Trained ]]/[@[Total ]],"")
C45:C57	C45	=IF(COUNT(B13:C13)=0,"",SUM($C$2:$C$3,B$12:$C13))

 

[denzo36]

You haven't shown your graph so not sure what type of graph it is but normally if you click on your column or line within your graph, right click and select 'select data' there's an option for 'hidden and empty cells' - from here you can play around with the different option to get it to work - you might want to set your zero to an empty cell using a formula and use the 'connect data points with line' option

 

[JamieP89]

You haven't shown your graph so not sure what type of graph it is but normally if you click on your column or line within your graph, right click and select 'select data' there's an option for 'hidden and empty cells' - from here you can play around with the different option to get it to work - you might want to set your zero to an empty cell using a formula and use the 'connect data points with line' option
View attachment 79239

Thanks for coming back to me - I ended up figuring out the solution was to change my =IF(COUNT) function needed to have the if true changed from "" to #N/A so that it doesn't count it as a number and it fixed the chart