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