Newbie to charts question - projecting values between data points

Excel 2002 SP2
Windows XP Pro

I'm trying to make a line chart showing data points of two series.

My X-axis labels are years, from 1970 through 2010.
My Y-axis labels are population values.

The first series (Series1) comes from actual data values in a column on 
another sheet.

The second series (Series2) are calculated values that are in a column next 
to the values for the first series.

Concerning just the first series:

First of all, for some reason the data points are appearing on the chart but 
the line between points is not.

Secondly, and perhaps explaining the above, not every year value has a 
population value.  In these cases the cell for that particular year is blank 
and does not contain a zero or any other value.

My aim is twofold, 1) to simply make a chart showing at least the Series1 
data points with lines connecting the points, and 2) to try to impose a sort 
of trendline between the first and last points that *must* pass through each 
data point.  I've managed to put some trendlines on the chart using Excel's 
built in Trendline option, but they don't necessarily include the data 
points.  Is there a way to force a line that passes through all the data 
points?  Perhaps a trendline is a poor choice of words, maybe more like a 
projection line.

Anyway, any help, suggestions or links would be greatly appreciated.

Thanks!

-gk-


=================================================
The creative act is not the province of remote oracles or rarefied geniuses
but a transparent process that is open to everyone.
-Greg Kot in Wilco Learning How To Die- 


0
gkammerer (4)
1/5/2005 3:15:23 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
450 Views

Similar Articles

[PageSpeed] 47

Greg -

To make the lines appear (assuming the intervening cells are truly blank, and not 
just formulas that appear blank to the human eye), select the chart, go to Options 
on the Tools menu, and on the Chart tab, choose the Show Empty Cells As -- 
Interpolate option.

A trendline will not pass through all points of any but the most mundane data sets. 
You can make it extend beyond the data: double click the trendline, click on the 
Options tab, and choose the number of units to forecast forward or backward 
(back-cast?). You could use a smoothed line to make it look like a "best fit through 
all points" curve.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

38N90W wrote:

> Excel 2002 SP2
> Windows XP Pro
> 
> I'm trying to make a line chart showing data points of two series.
> 
> My X-axis labels are years, from 1970 through 2010.
> My Y-axis labels are population values.
> 
> The first series (Series1) comes from actual data values in a column on 
> another sheet.
> 
> The second series (Series2) are calculated values that are in a column next 
> to the values for the first series.
> 
> Concerning just the first series:
> 
> First of all, for some reason the data points are appearing on the chart but 
> the line between points is not.
> 
> Secondly, and perhaps explaining the above, not every year value has a 
> population value.  In these cases the cell for that particular year is blank 
> and does not contain a zero or any other value.
> 
> My aim is twofold, 1) to simply make a chart showing at least the Series1 
> data points with lines connecting the points, and 2) to try to impose a sort 
> of trendline between the first and last points that *must* pass through each 
> data point.  I've managed to put some trendlines on the chart using Excel's 
> built in Trendline option, but they don't necessarily include the data 
> points.  Is there a way to force a line that passes through all the data 
> points?  Perhaps a trendline is a poor choice of words, maybe more like a 
> projection line.
> 
> Anyway, any help, suggestions or links would be greatly appreciated.
> 
> Thanks!
> 
> -gk-
> 
> 
> =================================================
> The creative act is not the province of remote oracles or rarefied geniuses
> but a transparent process that is open to everyone.
> -Greg Kot in Wilco Learning How To Die- 
> 
> 

0
1/5/2005 4:19:35 AM
"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message 
news:%23FKZF3t8EHA.2196@TK2MSFTNGP14.phx.gbl...
> Greg -
>
> To make the lines appear (assuming the intervening cells are truly blank, 
> and not just formulas that appear blank to the human eye), select the 
> chart, go to Options on the Tools menu, and on the Chart tab, choose the 
> Show Empty Cells As -- 
> Interpolate option.
>
> A trendline will not pass through all points of any but the most mundane 
> data sets. You can make it extend beyond the data: double click the 
> trendline, click on the Options tab, and choose the number of units to 
> forecast forward or backward (back-cast?). You could use a smoothed line 
> to make it look like a "best fit through all points" curve.
>
> - Jon

Thanks Jon!!

Are there any AddIns for doing Chart analysis?

-gk-


0
gkammerer (4)
1/5/2005 11:39:03 PM
Greg -

What kind of analysis are you looking for?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

38N90W wrote:

> "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message 
> news:%23FKZF3t8EHA.2196@TK2MSFTNGP14.phx.gbl...
> 
>>Greg -
>>
>>To make the lines appear (assuming the intervening cells are truly blank, 
>>and not just formulas that appear blank to the human eye), select the 
>>chart, go to Options on the Tools menu, and on the Chart tab, choose the 
>>Show Empty Cells As -- 
>>Interpolate option.
>>
>>A trendline will not pass through all points of any but the most mundane 
>>data sets. You can make it extend beyond the data: double click the 
>>trendline, click on the Options tab, and choose the number of units to 
>>forecast forward or backward (back-cast?). You could use a smoothed line 
>>to make it look like a "best fit through all points" curve.
>>
>>- Jon
> 
> 
> Thanks Jon!!
> 
> Are there any AddIns for doing Chart analysis?
> 
> -gk-
> 
> 

0
1/6/2005 5:15:07 AM
Reply:

Similar Artilces:

Newbie, accnts. payable & accnts.receivable sample DB
Hi guys, I�m running access 2003, windows xp pro SP2 I've been commission in my work to do a small application to track and report the accounts payable and accounts receivable movements, I'm not an accounting savvy person :(, does anyone know of a link or sample access database that can help me with the basic structure of the tables and reports?, any help would be greatly appreciated. Thanks ...

Really simple questions re CSliderCtrl
Using SetSelection() I can get a CSliderCtrl to display a highlighted selection ... but so what? What use is it other than to look pretty? Which of the following is possible, and how do I do them? (1) Prevent the user from moving the thumb outside the highlighted range. (This seems to be what the documentation suggests that SetSelection does, but in fact it doesn't, the thumb can be moved outside the selection.) (2) Have the user change the selection. (The documentation seems to suggest this is possible, but I can find no combination of keystrokes or mouse actions that will do it.) -- ...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

#VALUE! #3
I want A1 to report an error that is in A6 but my formula doesn't work. A6 reads: #VALUE! The formula A1 is =IF(AN6=#VALUE!,"You messed up","") Help. Try =IF(ISERROR(AN6),"You messed up","") -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Pencil" <pencil@unlisted.com> wrote in message news:Xns981091707C316pencilunlistedcom@208.49.80.60... >I want A1 to report an error that is in A6 but my formula doesn't work. > A6 reads: #VALUE! > The formula A1 is =IF(AN6=...

Broken lines in a chart to display extreme values?
Sometimes a single extreme value causes the rest of the data to become too small, like a bar chart of the values (10000, 4, 3, 2, 4, 5). The way to avoid is to use broken lines where the axis makes a leap from 10 to 9990 with a dotted gap inbetween. I couldn't find a way to implement this in Excel. Is there a way? Hi, It is not a built in option but can be constructed. http://www.andypope.info/charts/brokencolumn.htm Cheers Andy Metin Örsel wrote: > Sometimes a single extreme value causes the rest of the data to become too > small, like a bar chart of the values (10000,...

Macro to change pivot field with latest value 03-01-10
This is a re-post as my questions hasn't been answered by anyone... Hi there and thank you in advance for any help. I have written a macro that changes (hides / shows) fields on a pivot table. One of these changes the field used for the page fields section of the pivot table. However, when i add a field to this section, it defaults to All. How can have this field default to the latest value in this field. Let me explain further: I want the pivot to be shown either by date or by week. The macro then changes the page field section to show either the date or week field. If ...

goal seek wont calculate an accurate value past 3 decimal places
I have a traditional situation in which to use goal seek. I need the value in one cell to equal a certain value by changing another cell. But the value I want it to go to is very small like 0.04459. So when I click calculate goal seek gets the first 3 decimal places correct, but the remaining ones are off. So does goal seek not calculate past 3 decimal places or am I doing something wrong? Any help would be appreciated. Thanks. Joe Hi Joe Check column widths for starters and then have a look at cell formatting for number of decimal places. Mine works fine, Office 2000 HTH Michael &quo...

X axis labels won't line up under corresponding column data
Only way I can get dates (year 1998, 1999 and on etc) to fall under annual income columns is to horse around with spacing, adding and subtracting spaces in the category axis drop-down box in Chart Options. Seems you can't even use the same number of spaces between each because if you add between two of them, adding the same number of spaces doesn'tnecessarily work between the next two. I have about 8 charts to mess with and can't find an easy way to do this. did you try this righclcik x axis, clcik <format axis> choose <scale> UNCHECK <value(y) axis crosed be...

buble chart
I want to take data from excell or a Database to create a buble chart. One piece of Data would determin the size of the Circle. another piecie of data determin the location location of the circle on the sheet- Can it be done? we spell it a little different. Try over here http://blogs.msdn.com/visio/archive/2007/02/06/introducing-the-bubble-chart.aspx "Brent" <sagemaster@mvdsl.com> wrote in message news:uLTAeOZkHHA.5084@TK2MSFTNGP03.phx.gbl... >I want to take data from excell or a Database to create a buble chart. One >piece of Data would determin the size of...

Creating a Separate Chart for Each Row of Data
I need to create 150 similar charts for 150 separate rows of data contained in an Excel 2003 spreadsheet. I am not an accomplished Macro author. Any bright ideas out there? Need a bit more info. Can you post a sample of the data you have including column headers etc? What sort of chart do you want? Do you need to be able to view all or multiple charts simultaneously. Under some circumstances using AutoFilter allows you to create just one chart and it becomes dynamic based on the AutoFilter setting. If AutoFilter option used, do you have a column with unique values or titles that wou...

Visio Newbie
Hello all, this is my first time using Visio for drawing a floor plan and I have a quick question. Yesterday, (Using a tape measure) I mapped out the floor plan of an office my company might be moving into. I wrote everything down in Inches. Is there a way once I get a room shape kinda close to teh measurements, that I can manually type in teh length for each wall? if not in inches, at least in feet. For example, I have one wall that is 154 inches ( 12.83 ft) Then the next wall is 242 incehs( 20.1 ft) The opposite wall of the first measurement is 288 inches, third wall 100 inches, then 13...

Outlook 2000 and data
Hello all, I have been using Outlook 2000 for a couple of years and I am at the stage now when I backup (export to a .pst file) that the file is larger than a 700mb CD which I copy the backup to. How can I break up the data in to smaller amounts so that I can copy the data to two CDs or more? Thankyou Anton Anton wrote: > Hello all, > > I have been using Outlook 2000 for a couple of years and I am at the > stage now when I backup (export to a .pst file) that the file is > larger than a 700mb CD which I copy the backup to. How can I break up > the data in to smaller amou...

Printing question
We are having an issue with LPR printing using our software in conjucntion with a Windows 2008 server which is preventing us from being able to flash images to a printer from our application. We use Universe, SB+ and SBClient and would use the DOS shell from inside SBClient to use LPR print commands to send the images to the printer, this has worked fine on all other Windows servers but not 2008. What happens is when you drop to DOS you can browse to the %systemroot%\system32 folder and do a dir but you cannot see LPR. If you do the same from a normal command prompt the file ...

Chart niggle #2
Good evening all, I have created charts using JW's 'Chart From Combo Box' in chapter 15 from his Excel 2000 functions CD. This has worked great, being able to choose the required chart from the drop-down list, which I have adapted. However, the 'chart titles' do not change to the appropriate value when I choose the suitable chart, but remain the same throughout my choices, eg every chart title is called 'March', when in fact I am looking at a chart produced for February. I have tried naming the title in Options/Chart Title, but still all charts remain with the sa...

Line in chart into ONE arrow
I’m trying to make an ordinary line chart but instead of a line I would like an arrow pointing right in the direction the line is heading. So, the line would actually be one big arrow moving over the x-axis. Is it possible? Hi rick, Is this the sort of thing you mean? http://www.andypope.info/ngs/ng10.htm Cheers Andy rick spender wrote: > I’m trying to make an ordinary line chart but instead of a line I would like > an arrow pointing right in the direction the line is heading. So, the line > would actually be one big arrow moving over the x-axis. Is it possible? -- Andy Po...

Recordset
I have a dumb question that I cannot seem to find the answer to. I have a recordset that uses a field.value in the where clause. Can anyone tell me how to set up the recordset so if nothing is returned from my sql the vba does not fail. Example: recset = "select * from stores where store = form.store" If ???? NO STORE FOUND????? msgbox "store does not exist" else msgbox "store found, continue processing" end if Thanks!! If recset.RecordCount = 0 then .... HTH, Rob "AJ" <AJ@discussions.microsoft.com> wrote in message news:7FAB...

Org chart assistant position
I've recently been upgraded to Visio 2003 (not sure what the previous version was), and my org charts are swapping around. The assistants used to show up to the right of their managers, now they appear below. Can someone tell me where the setting is to tell the org chart that all assistants should appear directly to the right? ...

parse data from text field
I currently have a field in a form that contains the exact path to files stored on my computer (ex. C:\file1\file2\file3\samplefile.doc). The path to the file is different for most of the files. I have changed some things in the database and I would like to parse just the file name and extension (ex. samplefile.doc). I have tried the Mid, Left, Right, and InStr functions but I have not been able to get the desired data. Any help with code to parse the data correctly would be appreciated. The names of files are not equal in length. Thank you accessuser1308, You kinda ...

C++ / Access Newbie
Hi Folks... I have a small commercial package written in C++ (targeted to Windows platforms) that uses its own proprietary database. We are just starting to investigate the option of replacing our database with a commercial package, such as Access or MySQL. Can anyone recommend informative reading that provides details on programming C++ with either of these two databases? At this stage we have no (any I mean ZERO) experience such programming. Any information would be helpful: recommendations for Access over MySQL (or vice versa), sources of such comparisons, licensing considerations, OD...

Exchange newbie problem
Hi all; A user on our domain was deleted when he should not have been. His mail box was not deleted. When his account was recreated, we tried to reconnect the old mailbox. But when selecting a user in the "Select a new user for this mailbox" dialog a message comes back saying that the user cannot be found. We tried several different users a none can be found even though they can get there mail just fine. We need to get the messages from this mailbox. Any ideas would be great -- Craig in the High Desert. craig@craignet.com But it's a dry heat. you should be able to reconnec...

Charts #19
It has been about 2 years from the last time I worked with Excel. At that time I had lots of charts and lots of worksheets tracking units, production and so on. I would have 2 or 3 master sheets that took data from each sheet. So here is my issue. I have made a workbook for myself and need to chart like data from the months but when I try to get the serries I keep getting errors. So the way I did it before is not working. Any Ideas on how I can get this chart to work as I need it? Zyl What are you trying to do? How are you trying to do it? What does the data look like? Where is the d...

Chart size
Is there a way to make the User defined chart be made a specific size when created? We will be making multiple charts and I wish them all to be a specific size as they are then copied to Word. Thank you Deborah - If you make the chart with the Chart Wizard, you are stuck with the default size and position, which is *approximately* half of the height and width of the active pane, centered within the pane. You can resize charts by dragging their corners, and if you hold down the Alt key, the chart edges stay aligned with cell boundaries, so you could make the charts the same size this ...

newbie
joining the group Hey Newbie where are you from and what versiona re you using? Fliehigh "cthomas23@hotmail.com" wrote: > joining the group > ...

Display Formula Values
I have a number of formulas that are displaying the result using the value that is displayed in a referenced cell rather than what was actually input. For example, if my formula in cell A1 is B1*C1 and my value in C1 is .075 and the cell is formatted to display a percentage with no decimal places my formula in B1 uses 8% instead of 7.5%. How can I address this across the entire workbook rather than having to change the format in each of the necessary cells. Thanks for your help. What formula do you have in B1? -- Regards, RD --------------------------------------------------------...

General Question
Is this the only way to get Outlook help without having to pay? There are lots of websites with information posted. http://www.slipstick.com for one. Of course there's always http://www.microsoft.com/outlook. And the online help files in Outlook. (Press F1) -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Amy" <anonymous@discussions.microsoft.com> wrote in message news:03c701c3a959$cafe41f0$a3012...