How can I ignore future zero values in chart?

[Ironman]

Hi

I have the below range that feeds a chart. Each cell represents a week and we're in Week 1 now. The source of the cells is in the second table below.

Exercise Log.xlsm
	AN
316	2023
317	9
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
Weekly Tracking

Cell Formulas
Range	Formula
AN317	AN317	=AN2
AN318	AN318	=AN7
AN319	AN319	=AN12
AN320	AN320	=AN17
AN321	AN321	=AN22
AN322	AN322	=AN27
AN323	AN323	=AN32
AN324	AN324	=AN37
AN325	AN325	=AN42
AN326	AN326	=AN47
AN327	AN327	=AN52
AN328	AN328	=AN57
AN329	AN329	=AN62
AN330	AN330	=AN67
AN331	AN331	=AN72
AN332	AN332	=AN77
AN333	AN333	=AN82
AN334	AN334	=AN87
AN335	AN335	=AN92
AN336	AN336	=AN97
AN337	AN337	=AN102
AN338	AN338	=AN107
AN339	AN339	=AN112
AN340	AN340	=AN117
AN341	AN341	=AN122
AN342	AN342	=AN127
AN343	AN343	=AN132
AN344	AN344	=AN137
AN345	AN345	=AN142
AN346	AN346	=AN147
AN347	AN347	=AN152
AN348	AN348	=AN157
AN349	AN349	=AN162
AN350	AN350	=AN167
AN351	AN351	=AN172
AN352	AN352	=AN177
AN353	AN353	=AN182
AN354	AN354	=AN187
AN355	AN355	=AN192
AN356	AN356	=AN197
AN357	AN357	=AN202
AN358	AN358	=AN207
AN359	AN359	=AN212
AN360	AN360	=AN217
AN361	AN361	=AN222
AN362	AN362	=AN227
AN363	AN363	=AN232
AN364	AN364	=AN237
AN365	AN365	=AN242
AN366	AN366	=AN247
AN367	AN367	=AN252
AN368	AN368	=AN257

Exercise Log.xlsm
	AN
1	2023
2	9
3	22
4	9
5	22
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
Weekly Tracking

Cell Formulas
Range	Formula
AN2	AN2	=IF(COUNTA('Daily Tracking'!AQ3:AQ9),SUM('Daily Tracking'!AQ3:AQ9),"")
AN3	AN3	=IF(AN2="","",RANK(AN2,$B$2:$BZ$2))
AN4	AN4	=IF(AN2="","",AN2)
AN5,AN255,AN250,AN245,AN240,AN235,AN230,AN225,AN220,AN215,AN210,AN205,AN200,AN195,AN190,AN185,AN180,AN175,AN170,AN165,AN160,AN155,AN150,AN145,AN140,AN135,AN130,AN125,AN120,AN115,AN110,AN105,AN100,AN95,AN90,AN85,AN80,AN75,AN70,AN65,AN60,AN55,AN50,AN45,AN40	AN5	=IF(AN4="","",RANK(AN4,$B4:$BZ4))
AN7	AN7	=IF(COUNTA('Daily Tracking'!AQ10:AQ16),SUM('Daily Tracking'!AQ10:AQ16),"")
AN8	AN8	=IF(AN7="","",RANK(AN7,$B$7:$BZ$7))
AN9,AN254,AN249,AN244,AN239,AN234,AN229,AN224,AN219,AN214,AN209,AN204,AN199,AN194,AN189,AN184,AN179,AN174,AN169,AN164,AN159,AN154,AN149,AN144,AN139,AN134,AN129,AN124,AN119,AN114,AN109,AN104,AN99,AN94,AN89,AN84,AN79,AN74,AN69,AN64,AN59,AN54,AN49,AN44,AN39	AN9	=IF(AN7="","",AVERAGE(IF(MOD(ROW(AN$1:AN7),5)=2,AN$1:AN7)))
AN12	AN12	=IF(COUNTA('Daily Tracking'!AQ17:AQ23),SUM('Daily Tracking'!AQ17:AQ23),"")
AN13	AN13	=IF(AN12="","",RANK(AN12,$B$12:$BZ$12))
AN17	AN17	=IF(COUNTA('Daily Tracking'!AQ24:AQ30),SUM('Daily Tracking'!AQ24:AQ30),"")
AN18	AN18	=IF(AN17="","",RANK(AN17,$B$17:$BZ$17))
AN22	AN22	=IF(COUNTA('Daily Tracking'!AQ31:AQ37),SUM('Daily Tracking'!AQ31:AQ37),"")
AN23	AN23	=IF(AN22="","",RANK(AN22,$B$22:$BZ$22))
AN27	AN27	=IF(COUNTA('Daily Tracking'!AQ38:AQ44),SUM('Daily Tracking'!AQ38:AQ44),"")
AN28	AN28	=IF(AN27="","",RANK(AN27,$B$27:$BZ$27))
AN32	AN32	=IF(COUNTA('Daily Tracking'!AQ45:AQ51),SUM('Daily Tracking'!AQ45:AQ51),"")
AN33	AN33	=IF(AN32="","",RANK(AN32,$B$32:$BZ$32))
AN37	AN37	=IF(COUNTA('Daily Tracking'!AQ52:AQ58),SUM('Daily Tracking'!AQ52:AQ58),"")
AN38	AN38	=IF(AN37="","",RANK(AN37,$B$37:$BZ$37))
AN42	AN42	=IF(COUNTA('Daily Tracking'!AQ59:AQ66),SUM('Daily Tracking'!AQ59:AQ66),"")
AN43	AN43	=IF(AN42="","",RANK(AN42,$B$42:$BZ$42))
AN47	AN47	=IF(COUNTA('Daily Tracking'!AQ67:AQ73),SUM('Daily Tracking'!AQ67:AQ73),"")
AN48	AN48	=IF(AN47="","",RANK(AN47,$B$47:$BZ$47))
AN52	AN52	=IF(COUNTA('Daily Tracking'!AQ74:AQ80),SUM('Daily Tracking'!AQ74:AQ80),"")
AN53	AN53	=IF(AN52="","",RANK(AN52,$B$52:$BZ$52))
AN57	AN57	=IF(COUNTA('Daily Tracking'!AQ81:AQ87),SUM('Daily Tracking'!AQ81:AQ87),"")
AN58	AN58	=IF(AN57="","",RANK(AN57,$B$57:$BZ$57))
AN62	AN62	=IF(COUNTA('Daily Tracking'!AQ88:AQ94),SUM('Daily Tracking'!AQ88:AQ94),"")
AN63	AN63	=IF(AN62="","",RANK(AN62,$B$62:$BZ$62))
AN67	AN67	=IF(COUNTA('Daily Tracking'!AQ95:AQ101),SUM('Daily Tracking'!AQ95:AQ101),"")
AN68	AN68	=IF(AN67="","",RANK(AN67,$B$67:$BZ$67))
AN72	AN72	=IF(COUNTA('Daily Tracking'!AQ102:AQ108),SUM('Daily Tracking'!AQ102:AQ108),"")
AN73	AN73	=IF(AN72="","",RANK(AN72,$B$72:$BZ$72))
AN77	AN77	=IF(COUNTA('Daily Tracking'!AQ109:AQ115),SUM('Daily Tracking'!AQ109:AQ115),"")
AN78	AN78	=IF(AN77="","",RANK(AN77,$B$77:$BZ$77))
AN82	AN82	=IF(COUNTA('Daily Tracking'!AQ116:AQ122),SUM('Daily Tracking'!AQ116:AQ122),"")
AN83	AN83	=IF(AN82="","",RANK(AN82,$B$82:$BZ$82))
AN87	AN87	=IF(COUNTA('Daily Tracking'!AQ123:AQ129),SUM('Daily Tracking'!AQ123:AQ129),"")
AN88	AN88	=IF(AN87="","",RANK(AN87,$B$87:$BZ$87))
AN92	AN92	=IF(COUNTA('Daily Tracking'!AQ130:AQ136),SUM('Daily Tracking'!AQ130:AQ136),"")
AN93	AN93	=IF(AN92="","",RANK(AN92,$B$92:$BZ$92))
AN97	AN97	=IF(COUNTA('Daily Tracking'!AQ137:AQ143),SUM('Daily Tracking'!AQ137:AQ143),"")
AN98	AN98	=IF(AN97="","",RANK(AN97,$B$97:$BZ$97))
AN102	AN102	=IF(COUNTA('Daily Tracking'!AQ144:AQ150),SUM('Daily Tracking'!AQ144:AQ150),"")
AN103	AN103	=IF(AN102="","",RANK(AN102,$B$102:$BZ$102))
AN107	AN107	=IF(COUNTA('Daily Tracking'!AQ151:AQ157),SUM('Daily Tracking'!AQ151:AQ157),"")
AN108	AN108	=IF(AN107="","",RANK(AN107,$B$107:$BZ$107))
AN112	AN112	=IF(COUNTA('Daily Tracking'!AQ158:AQ164),SUM('Daily Tracking'!AQ158:AQ164),"")
AN113	AN113	=IF(AN112="","",RANK(AN112,$B$112:$BZ$112))
AN117	AN117	=IF(COUNTA('Daily Tracking'!AQ165:AQ171),SUM('Daily Tracking'!AQ165:AQ171),"")
AN118	AN118	=IF(AN117="","",RANK(AN117,$B$117:$BZ$117))
AN122	AN122	=IF(COUNTA('Daily Tracking'!AQ172:AQ178),SUM('Daily Tracking'!AQ172:AQ178),"")
AN123	AN123	=IF(AN122="","",RANK(AN122,$B$122:$BZ$122))
AN127	AN127	=IF(COUNTA('Daily Tracking'!AQ179:AQ185),SUM('Daily Tracking'!AQ179:AQ185),"")
AN128	AN128	=IF(AN127="","",RANK(AN127,$B$127:$BZ127))
AN132	AN132	=IF(COUNTA('Daily Tracking'!AQ186:AQ192),SUM('Daily Tracking'!AQ186:AQ192),"")
AN133	AN133	=IF(AN132="","",RANK(AN132,$B$132:$BZ$132))
AN137	AN137	=IF(COUNTA('Daily Tracking'!AQ193:AQ199),SUM('Daily Tracking'!AQ193:AQ199),"")
AN138	AN138	=IF(AN137="","",RANK(AN137,$B$137:$BZ$137))
AN142	AN142	=IF(COUNTA('Daily Tracking'!AQ200:AQ206),SUM('Daily Tracking'!AQ200:AQ206),"")
AN143	AN143	=IF(AN142="","",RANK(AN142,$B$142:$BZ$142))
AN147	AN147	=IF(COUNTA('Daily Tracking'!AQ207:AQ213),SUM('Daily Tracking'!AQ207:AQ213),"")
AN148	AN148	=IF(AN147="","",RANK(AN147,$B$147:$BZ$147))
AN152	AN152	=IF(COUNTA('Daily Tracking'!AQ214:AQ220),SUM('Daily Tracking'!AQ214:AQ220),"")
AN153	AN153	=IF(AN152="","",RANK(AN152,$B$152:$BZ$152))
AN157	AN157	=IF(COUNTA('Daily Tracking'!AQ221:AQ227),SUM('Daily Tracking'!AQ221:AQ227),"")
AN158	AN158	=IF(AN157="","",RANK(AN157,$B$157:$BZ$157))
AN162	AN162	=IF(COUNTA('Daily Tracking'!AQ228:AQ234),SUM('Daily Tracking'!AQ228:AQ234),"")
AN163	AN163	=IF(AN162="","",RANK(AN162,$B$162:$BZ$162))
AN167	AN167	=IF(COUNTA('Daily Tracking'!AQ235:AQ241),SUM('Daily Tracking'!AQ235:AQ241),"")
AN168	AN168	=IF(AN167="","",RANK(AN167,$B$167:$BZ$167))
AN172	AN172	=IF(COUNTA('Daily Tracking'!AQ242:AQ248),SUM('Daily Tracking'!AQ242:AQ248),"")
AN173	AN173	=IF(AN172="","",RANK(AN172,$B$172:$BZ$172))
AN177	AN177	=IF(COUNTA('Daily Tracking'!AQ249:AQ255),SUM('Daily Tracking'!AQ249:AQ255),"")
AN178	AN178	=IF(AN177="","",RANK(AN177,$B$177:$BZ$177))
AN182	AN182	=IF(COUNTA('Daily Tracking'!AQ256:AQ262),SUM('Daily Tracking'!AQ256:AQ262),"")
AN183	AN183	=IF(AN182="","",RANK(AN182,$B$182:$BZ$182))
AN187	AN187	=IF(COUNTA('Daily Tracking'!AQ263:AQ269),SUM('Daily Tracking'!AQ263:AQ269),"")
AN188	AN188	=IF(AN187="","",RANK(AN187,$B$187:$BZ$187))
AN192	AN192	=IF(COUNTA('Daily Tracking'!AQ270:AQ276),SUM('Daily Tracking'!AQ270:AQ276),"")
AN193	AN193	=IF(AN192="","",RANK(AN192,$B$192:$BZ$192))
AN197	AN197	=IF(COUNTA('Daily Tracking'!AQ277:AQ283),SUM('Daily Tracking'!AQ277:AQ283),"")
AN198	AN198	=IF(AN197="","",RANK(AN197,$B$197:$BZ$197))
AN202	AN202	=IF(COUNTA('Daily Tracking'!AQ284:AQ290),SUM('Daily Tracking'!AQ284:AQ290),"")
AN203	AN203	=IF(AN202="","",RANK(AN202,$B$202:$BZ$202))
AN207	AN207	=IF(COUNTA('Daily Tracking'!AQ291:AQ297),SUM('Daily Tracking'!AQ291:AQ297),"")
AN208	AN208	=IF(AN207="","",RANK(AN207,$B$207:$BZ$207))
AN212	AN212	=IF(COUNTA('Daily Tracking'!AQ298:AQ304),SUM('Daily Tracking'!AQ298:AQ304),"")
AN213	AN213	=IF(AN212="","",RANK(AN212,$B$212:$BZ$212))
AN217	AN217	=IF(COUNTA('Daily Tracking'!AQ305:AQ311),SUM('Daily Tracking'!AQ305:AQ311),"")
AN218	AN218	=IF(AN217="","",RANK(AN217,$B$217:$BZ$217))
AN222	AN222	=IF(COUNTA('Daily Tracking'!AQ312:AQ318),SUM('Daily Tracking'!AQ312:AQ318),"")
AN223	AN223	=IF(AN222="","",RANK(AN222,$B$222:$BZ$222))
AN227	AN227	=IF(COUNTA('Daily Tracking'!AQ319:AQ325),SUM('Daily Tracking'!AQ319:AQ325),"")
AN228	AN228	=IF(AN227="","",RANK(AN227,$B$227:$BZ$227))
AN232	AN232	=IF(COUNTA('Daily Tracking'!AQ326:AQ332),SUM('Daily Tracking'!AQ326:AQ332),"")
AN233	AN233	=IF(AN232="","",RANK(AN232,$B$232:$BZ$232))
AN237	AN237	=IF(COUNTA('Daily Tracking'!AQ333:AQ339),SUM('Daily Tracking'!AQ333:AQ339),"")
AN238	AN238	=IF(AN237="","",RANK(AN237,$B$237:$BZ$237))
AN242	AN242	=IF(COUNTA('Daily Tracking'!AQ340:AQ346),SUM('Daily Tracking'!AQ340:AQ346),"")
AN243	AN243	=IF(AN242="","",RANK(AN242,$B$242:$BZ$242))
AN247	AN247	=IF(COUNTA('Daily Tracking'!AQ347:AQ353),SUM('Daily Tracking'!AQ347:AQ353),"")
AN248	AN248	=IF(AN247="","",RANK(AN247,$B$247:$BZ$247))
AN252	AN252	=IF(COUNTA('Daily Tracking'!AQ354:AQ360),SUM('Daily Tracking'!AQ354:AQ360),"")
AN253	AN253	=IF(AN252="","",RANK(AN252,$B$252:$BZ$252))
AN257	AN257	=IF(COUNTA('Daily Tracking'!AQ361:AQ367),SUM('Daily Tracking'!AQ361:AQ367),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL252:AN252,AL257:AN257	Expression	=IF(OR(AL252<39.5,AL252=""),1,0)	text	YES
AL252:AN252,AL257:AN257	Cell Value	between 39.5 and 44.9	text	YES
AL252:AN252,AL257:AN257	Cell Value	between 45 and 49.9	text	YES
AL252:AN252,AL257:AN257	Cell Value	between 39.5 and 44.9	text	YES
AL252:AN252,AL257:AN257	Cell Value	between 45 and 49.9	text	YES
AL255:AN255,AL253:AN253,AL258:AN258	Cell Value	=1	text	YES
AL255:AN255,AL253:AN253,AL258:AN258	Cell Value	=2	text	YES
AL255:AN255,AL253:AN253,AL258:AN258	Cell Value	=3	text	YES
AL254:AN254	Expression	=IF(OR(AL254<39.5,AL254=""),1,0)	text	YES
AL254:AN254	Cell Value	between 39.5 and 44.9	text	YES
AL254:AN254	Cell Value	between 45 and 49.9	text	YES
AL254:AN254	Cell Value	between 39.5 and 44.9	text	YES
AL254:AN254	Cell Value	between 45 and 49.9	text	YES

Right now every week (AN318 onwards) shows as a zero value in the chart and I want the chart to only show data up to and including the current week. I have looked around online and tried amending the formula so it shows #N/A and I then Select Data in the chart and Hidden and Empty Cell Settings and check Show #N/A as an empty cell, but I still see the zero values.

Hope you can help!

Many thanks.

 

[etaf]

#n/a should work
but i cannot see that in your data

replace the formula so instead of a blank you get the NA

 

[Ironman]

Hi Etaf, thanks for your input. Initially it didn't work, but I forgot to check the #N/A box so it works fine now, many thanks!

 

[etaf]

did #N/A appear in the sheet used for the graph ?

should work

 

[Ironman]

Yes it did thanks Etaf, I'm now conditionally formatting the ranges so the cells appear blank instead i.e. I'm not blinded by the hundreds of #N/As (it still works) (I tried amending the formula to

=IF(AN7=#N/A,"",

but that didn't work).

 

[etaf]

does it work now ?

when i had to do this - I actually copied the data to a new sheet and kept it all away from the main sheet - so it was invisable to anyone who wanted to see
and the graphs taken from those sheets - plus a few other tricks

that was when i was putting board dashboards together - which had to be exported to Powerpoint along with the spreadsheet - so easier to maintain and show directors and board members

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed