Love Numbers Hate Graphs.

[Irwell]

I love data tables but hate graphs but naturally most people love a good graph so I'll do as I'm requested

I have a data table with headings of week commencing dates (Horizontal Axis) then under that I have numbers (Vertical Axis)..

Now at the moment only the first 4 weeks have valid data in, the rest are formulas awaiting future data.

My line graph that I quickly created obviously shows a drop and a flat line post the 4 weeks, hope that makes sense.

I'd like it to be that when the data updates or we pass a specific date that it automatically updates the chart without ever having the drop and flatline. The only way I know of is adapting the table week on week which is what I've been doing but was wondering if there is a simpler way that I don't know about?

Many thanks,
Simon

 

[GraH]

Hi, I suspect you have formula returning the data. So if a value is "0" (zero) replace it by NA(). Those are not plotted on the chart.
See difference below.

 

[Irwell]

Ah okay. I'll have a look shortly, just in one of many teams/skypes/zoom meetings

 

[GraH]

I wish I was. I'm on temporarily lay-off.

 

[Jon Peltier]

Also, if your formula returns "", it may look like the cell is blank, but it contains a very short length of text, and Excel plots text as zero. So replace "" in your formulas with NA(). That gives you an ugly #N/A error in the cell, but you could use conditional formatting to hide that.

 

[GraH]

Thank you @Jon Peltier for the complementary comment. Should have included that in my reply. Also Thank you btw for all the wit you've shared on your site. Love it.

 

[Irwell]

Thank you both and sorry to hear about your lay off GraH

 

[Irwell]

It didn't quite work..

The table data is a countifs function so it's returning a 0 if it doesn't count anything for that particular week.

=COUNTIFS('COVID-19 Flex 1'!$P:$P,Data!$A5,'COVID-19 Flex 1'!$Q:$Q,B$3)

The first condition is the week number on the data table.
The second condition is a formula on the second tab that returns week number.

So above the dates is a hidden line with a week number formula relating to the date.

	Numbers Triaged Week on Week of COVID-19 Lockdown
	16-Mar	23-Mar	30-Mar	06-Apr	13-Apr	20-Apr	27-Apr	04-May	11-May	18-May	25-May	01-Jun
Structured	17	34	27	9	0	0	0	0	0	0	0	0
Extended brief intervention	2	4	1	0	0	0	0	0	0	0	0	0
Non-structured	0	0	1	0	0	0	0	0	0	0	0	0

 

[Irwell]

Sorry - Count condition 1 is type of assessment i.e. structured.

 

[GraH]

Consider = IF (COUNTIFS('COVID-19 Flex 1'!$P:$P,Data!$A5,'COVID-19 Flex 1'!$Q:$Q,B$3)<> 0, COUNTIFS('COVID-19 Flex 1'!$P:$P,Data!$A5,'COVID-19 Flex 1'!$Q:$Q,B$3), NA() )