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

 

[Irwell]

You're a star mate. Surely there is a neater way of doing it though

 

[Irwell]

One snag... What if one of the numbers is actually a 0...

 

[Irwell]

Ignore me, I'm now looking at an average formula which it wont work for. So need to rethink for this one.

 

[Irwell]

IF(AVERAGEIFS('Nurse Triage'!$BA:$BA,'Nurse Triage'!$BB:$BB,Data!B$7)

So this one is returning a div if in BB there is no week number matching current or future weeks.

I think I need something to make it ignore the DIV

Thoughts.

 

[Irwell]

Done it :D

=IF(ISERROR(AVERAGEIF('Nurse Triage'!$BB:$BB,Data!B$7,'Nurse Triage'!$BA:$BA)),NA(),(AVERAGEIF('Nurse Triage'!$BB:$BB,Data!B$7,'Nurse Triage'!$BA:$BA)))