If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ------------------------------------------------------------------------ isofuncurves's Profile: http://www.excelforum.com/member.php...o&userid=30749 View this thread: http://www.excelforum.com/showthread...hreadid=504189 |
#2
|
|||
|
|||
?-Change data series range as data is entered?
Hi,
Sounds like you need to use a dynamic named range for the chart data source. Here are a collection of webpages on the subject. http://peltiertech.com/Excel/Charts/Dynamics.html http://www.tushar-mehta.com/excel/ne...rts/index.html http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm Cheers Andy isofuncurves wrote: I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
|
|||
|
|||
?-Change data series range as data is entered?
Andy, Thank you. That is exactly what I was looking for. I do have one ploblem with the solution though. My data table contains formulas to generate the chart data. So CounA() is not working for me. Each cell, inclulding the "empty ones" contains a formula: I am defining my series by: =OFFSET('CIP CMG'!$G$3,0,0,COUNTA('CIP CMG'!$G:$G)-1) =OFFSET('CIP CMG'!$I$3,0,0,COUNTA('CIP CMG'!$I:$I)) =SERIES('CIP CMG'!$H$3,Metrics.xls'!CIP_Date,Metrics.xls'!Sum_T arget_Start,2) The data for the named ranges is not entered manually. It is generated by a formula. =IF(G21=TODAY(),COUNTIF(C$2:C$91,""&$G21),"") I assume COUNTA does not work because the cells are non-empty. Any ideas how to make this work with formulas in the cells? thanks, Carl. -- isofuncurves ------------------------------------------------------------------------ isofuncurves's Profile: http://www.excelforum.com/member.php...o&userid=30749 View this thread: http://www.excelforum.com/showthread...hreadid=504189 |
#4
|
|||
|
|||
?-Change data series range as data is entered?
Have you tried COUNT() instead of COUNTA()?
isofuncurves wrote: Andy, Thank you. That is exactly what I was looking for. I do have one ploblem with the solution though. My data table contains formulas to generate the chart data. So CounA() is not working for me. Each cell, inclulding the "empty ones" contains a formula: I am defining my series by: =OFFSET('CIP CMG'!$G$3,0,0,COUNTA('CIP CMG'!$G:$G)-1) =OFFSET('CIP CMG'!$I$3,0,0,COUNTA('CIP CMG'!$I:$I)) =SERIES('CIP CMG'!$H$3,Metrics.xls'!CIP_Date,Metrics.xls'!Sum _Target_Start,2) The data for the named ranges is not entered manually. It is generated by a formula. =IF(G21=TODAY(),COUNTIF(C$2:C$91,""&$G21),"" ) I assume COUNTA does not work because the cells are non-empty. Any ideas how to make this work with formulas in the cells? thanks, Carl. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
|
|||
|
|||
?-Change data series range as data is entered?
I thought of using countif() since all will be numeric integers 0. I haven't tried it yet as I'm scrambling to put together a presentation... Thanks! Carl -- isofuncurves ------------------------------------------------------------------------ isofuncurves's Profile: http://www.excelforum.com/member.php...o&userid=30749 View this thread: http://www.excelforum.com/showthread...hreadid=504189 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
automtically change ranges in formulas when new data is entered | JRoyer95 | Worksheet Functions | 5 | January 6th, 2006 06:14 PM |
I need more general XY point to point plotting than XY scatter in | spazminator | Charts and Charting | 12 | December 19th, 2005 05:00 PM |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |