Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding cells being included in a chart | Charts and Charting in Excel | |||
When building a chart Dates are been included that are not in thedate range | Excel Worksheet Functions | |||
Copying a chart included in a range | Excel Programming | |||
Avoiding empty cells showing as zero in line chart without na() | Charts and Charting in Excel | |||
Finding Cells that are included in Chart Ranges | Excel Programming |