Avoiding cells being included in a chart
 

I have a simple column chart which displays a value for each day of the
month taken from cells B19 to B49. Days 1 to 31 are taken from cells A19
to A49 and displayed on the X axis.

As the month progresses I wish to draw a horizontal line which shows the
average of the column B entries to date, ie on day 10 the 10 day average
will be calculated and a horizontal line drawn at the appropriate level
across the chart area from column 1 to column 10. The next day it would
be recalculated and then redrawn across from column 1 to column 11.

Unfortunately, my current attempt is not working as the line drops to
the X axis when cells in column B have not been entered. The structure is...

Cells A19 to A49 hold the days of the month: 1 to 31

I enter a value for each day starting from B19 and going up to B49.

F14 holds =SUM(B19:B49) to total B19 to B49

I14 holds =COUNT(B19:B49) to determine how many cells in B19 to B49 hold
a value

H14 holds =(F14/I14) to average the values entered in B19 to B49,
ignoring cells that have yet to have values entered

I then use =IF(B190,$H$14,"") in D19, and similar in the rows down to
row 49, to read H14 into all of the rows where column B has had a value
entered

From the entries in D19 to D49 I then draw a line graph using these
values against the Y axis.

Is anyone able to describe how I can avoid the line being drawn beyond
the point where I have entered data, yet still allow me to calculate the
average automatically?

Apologies if the above isn't over clear: this is the fourth attempt at
clarity!

TIA

--
F

Avoiding cells being included in a chart
 

You need to define a dynamic range which will show only those cells
with data to date.

Then you use that range(by name) in your series plotting.

Rather than explain it all I would suggest you go to Stephen Bullen's
site and download FUNCHRT1 example workbook.

It is in ZIP form.

http://www.oaltd.co.uk/Excel/Default.htm

Click on Charting Examples and scroll down to find the file.

Easy to figure out what you're doing and will make your charting life
a breeze.

Keep the URL because there is an awful pile of good stuff on his site.



Gord



On Thu, 08 Dec 2011 23:49:56 +0000, F <news@nowhere wrote:

I have a simple column chart which displays a value for each day of the
month taken from cells B19 to B49. Days 1 to 31 are taken from cells A19
to A49 and displayed on the X axis.

As the month progresses I wish to draw a horizontal line which shows the
average of the column B entries to date, ie on day 10 the 10 day average
will be calculated and a horizontal line drawn at the appropriate level
across the chart area from column 1 to column 10. The next day it would
be recalculated and then redrawn across from column 1 to column 11.

Unfortunately, my current attempt is not working as the line drops to
the X axis when cells in column B have not been entered. The structure is...

Cells A19 to A49 hold the days of the month: 1 to 31

I enter a value for each day starting from B19 and going up to B49.

F14 holds =SUM(B19:B49) to total B19 to B49

I14 holds =COUNT(B19:B49) to determine how many cells in B19 to B49 hold
a value

H14 holds =(F14/I14) to average the values entered in B19 to B49,
ignoring cells that have yet to have values entered

I then use =IF(B190,$H$14,"") in D19, and similar in the rows down to
row 49, to read H14 into all of the rows where column B has had a value
entered

From the entries in D19 to D49 I then draw a line graph using these
values against the Y axis.

Is anyone able to describe how I can avoid the line being drawn beyond
the point where I have entered data, yet still allow me to calculate the
average automatically?

Apologies if the above isn't over clear: this is the fourth attempt at
clarity!

TIA

Avoiding cells being included in a chart
 

On 09/12/2011 00:47 Gord Dibben wrote:

You need to define a dynamic range which will show only those cells
with data to date.

Then you use that range(by name) in your series plotting.

Rather than explain it all I would suggest you go to Stephen Bullen's
site and download FUNCHRT1 example workbook.

It is in ZIP form.

http://www.oaltd.co.uk/Excel/Default.htm

Click on Charting Examples and scroll down to find the file.

Easy to figure out what you're doing and will make your charting life
a breeze.

Keep the URL because there is an awful pile of good stuff on his site.

Thanks for that, I've had a look and the site is really useful. However,
I've solved the problem by using

=IF(B190,$H$14,#N/A)

rather than

=IF(B190,$H$14,"")

--
F