Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoiding cells being included in a chart
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(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 .. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoiding cells being included in a chart
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(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 .. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoiding cells being included in a chart
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(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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoiding cells being included in a chart
On 10/12/2011 06:40 Jim Cone wrote:
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! -- F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When building a chart Dates are been included that are not in thedate range | Excel Worksheet Functions | |||
Cells not included in formulas | Excel Discussion (Misc queries) | |||
Avoiding Calculating Cells on the Status bar | Excel Discussion (Misc queries) | |||
Avoiding empty cells showing as zero in line chart without na() | Charts and Charting in Excel | |||
AVOIDING DUPLICATES IN A RANGE OF CELLS | Excel Discussion (Misc queries) |