Avoiding cells being included in a chart

[F]

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(B19>0,$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

 

[Jim Cone]

Use a line-column chart. Include the average column as part of the source range.
"#N/A" is not plotted when used as data for a chart.
So modify your formula(s) to use #N/A as the alternate value instead of "".
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)





"F" <news@nowhere>
wrote in message

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(B19>0,$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

..

 

[Jim Cone]

Use a line-column chart. Include the average column as part of the source range.
"#N/A" is not plotted when used as data for a chart.
So modify your formula(s) to use #N/A as the alternate value instead of "".
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"F" <news@nowhere>
wrote in message

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(B19>0,$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

..

 

[Jim Cone]

Use a line-column chart. Include the average column as part of the source range.
"#N/A" is not plotted when used as data for a chart.
So modify your formula(s) to use #N/A as the alternate value instead of "".
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"F" <news@nowhere>
wrote in message
news[email protected]...

 

[F]

Use a line-column chart. Include the average column as part of the source range.
"#N/A" is not plotted when used as data for a chart.
So modify your formula(s) to use #N/A as the alternate value instead of "".

Brilliant: thank you! And so simple!