Trendlines #3

I am using Access-to-Excel automation
to bring in 2 Columns of data...Week Ending Dates,
& a Value.  These are the Source Data for a Vertical-Bar
Chart.

I have a 3rd column that uses this formula...
  =IF(LEN(A8)=0,"",VLOOKUP(A8,TheLook,2))
to get Values for a Trendline...in "Column C".

I am NOT strong in Excel Charts at all, as I do
mostly Access Programming.

How can I take this "3rd-column" and assign that
to a Trendline?

TIA - Bob

0
BobBarnes (48)
8/23/2004 3:13:53 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
668 Views

Similar Articles

[PageSpeed] 48

Bob -

Excel can calculate a trendline from a charted series, but I sense this 
isn't the trendline you mean. To add the data in column C as another 
series in the chart, choose Source Data from the Chart menu, and click 
on the Series tab. Click on the Add button, then click in the Values box 
and select the range of data for the new series. Click in the Name box 
and enter a name or click on a cell with the series name. If you are 
using the same dates, you don't need to change the category labels.

You should change your formula to avoid false blanks (""). The NA() 
function returns the #N/A error, which might look ugly in the sheet 
(conditional formatting can hide it) but doesn't plot as a zero in most 
chart types.

    =IF(LEN(A8)=0,NA(),VLOOKUP(A8,TheLook,2))

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

Bob Barnes wrote:
> I am using Access-to-Excel automation
> to bring in 2 Columns of data...Week Ending Dates,
> & a Value.  These are the Source Data for a Vertical-Bar
> Chart.
> 
> I have a 3rd column that uses this formula...
>   =IF(LEN(A8)=0,"",VLOOKUP(A8,TheLook,2))
> to get Values for a Trendline...in "Column C".
> 
> I am NOT strong in Excel Charts at all, as I do
> mostly Access Programming.
> 
> How can I take this "3rd-column" and assign that
> to a Trendline?
> 
> TIA - Bob
> 

0
8/24/2004 2:31:17 AM
Bob -

If you mean the added series is plotted as a column chart series, right 
click on the series, select Chart Type from the pop up menu, then select 
the chart type that you wish to use.

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

Bob Barnes wrote:
> Jon - Thank you.  This is working.
> 
> How can I get the Trendline to appear as a "Line", rather than a vertical Bar?
> 
> TIA - Bob
> 
> 
> "Jon Peltier" wrote:
> 
> 
>>Bob -
>>
>>Excel can calculate a trendline from a charted series, but I sense this 
>>isn't the trendline you mean. To add the data in column C as another 
>>series in the chart, choose Source Data from the Chart menu, and click 
>>on the Series tab. Click on the Add button, then click in the Values box 
>>and select the range of data for the new series. Click in the Name box 
>>and enter a name or click on a cell with the series name. If you are 
>>using the same dates, you don't need to change the category labels.
>>
>>You should change your formula to avoid false blanks (""). The NA() 
>>function returns the #N/A error, which might look ugly in the sheet 
>>(conditional formatting can hide it) but doesn't plot as a zero in most 
>>chart types.
>>
>>    =IF(LEN(A8)=0,NA(),VLOOKUP(A8,TheLook,2))
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Bob Barnes wrote:
>>
>>>I am using Access-to-Excel automation
>>>to bring in 2 Columns of data...Week Ending Dates,
>>>& a Value.  These are the Source Data for a Vertical-Bar
>>>Chart.
>>>
>>>I have a 3rd column that uses this formula...
>>>  =IF(LEN(A8)=0,"",VLOOKUP(A8,TheLook,2))
>>>to get Values for a Trendline...in "Column C".
>>>
>>>I am NOT strong in Excel Charts at all, as I do
>>>mostly Access Programming.
>>>
>>>How can I take this "3rd-column" and assign that
>>>to a Trendline?
>>>
>>>TIA - Bob
>>>
>>
>>

0
8/24/2004 4:36:21 PM
Reply:

Similar Artilces:

Trendline error???
Has anybody else had this problem? If one uses a 6 order polynomial as trendline and then s/he enters manually in an other column the same equation taken from the displayed trendline equation on the graph , the original trendline is different from the one manually entered. Excel plots the two different curves having the same equation!!! Please see an example at : http://www.geocities.com/audax/error.xls Thank you for the help. Gene Disregard the previous message. The problem is due to the rounding off. The trendline calculation is OK. Gene See my website for the use of LINEST to get coef...

Index Function #3
I;m sorry abouth the repetition. I am new at trying to post a file that can be shared with others. I beleive this will work: http://www.mediafire.com/?trf3x2zin04 Thanks >Why do the formulas return errors (1) when the array >name is used, and (2) when cells are pointed to >on another sheet? The #REF! error is being caused by the fact that the formula is in the same column as the first number in the named range _2x12. This is called the implicit intersection. So, it's using the 125 as the row_num argument. Since there aren't 125 rows in LumberDB it causes the #REF! err...

Formula Help? #3
hi all if i have the following table, what is a formula i can use to pick up both male and female Construction and total it all up, so it comes to 28? 1991 Full time part time casual self employed Male manufacturing 1 2 3 4 construction 2 3 4 5 wholesale 3 4 5 6 retail 4 5 6 7 communications 5 6 7 8 accomodation 6 7 8 9 finance 7 8 9 10 education 8 9 10 11 health 9 10 11 12 government 10 11 12 13 Female manufacturing 1 2 3 4 construction 2 3 4 5 wholesale 3 4 5 6 retail 4 5 6 7 communications 5 6 7 8 accomodation 6 7 8 9 finance 7 ...

testing #3
sorry all just testing my server Thanks Hi use microsoft.public.test.here for this -- Regards Frank Kabel Frankfurt, Germany "Jonh" <Joan@microsoft.net> schrieb im Newsbeitrag news:76069c3866b1d1cf656b1251a8add96e@news.meganetnews.com... > sorry all just testing my server > > Thanks > > ...

Excel won't open #3
About a month ago, I was using Excel, and closed the program in the normal manner and everything was fine. I tried to open the program about an hour later (not sure how - probably by clicking on a spreadsheet, rather than opening the program first), but it froze the computer. It rebooted easily when I Ctl-Alt-Deleted. Every time I tried to open Excel from then on, no matter whether I went through the Office Taskbar, the Start Menu, the Desktop shortcut or clicking on a spreadsheet, the computer froze. I checked the computer for viruses - none detected. I scanned the hard drive - no problems ...

Set max to 3 to retrieve last 3 most recent dates
I have a table X that contains person's blood pressure reading dates and sys/dia results. Many may have more than 5 BP readings. I want to return only the 3 most recent dates based on PersonID. PersonID BPDate sys dia 1 4/3/2009 130 70 1 5/24/2009 145 80 2 4/22/2009 136 90 2 6/10/2009 136 90 2 9/23/2009 136 90 2 11/01/2009 136 90 Also to group the...

Outlook not closing #3
I close Outlook 2000 when I leave for the evening but it doesn't really quit. I have to go to the task manager and kill outlook.exe. Our whole office has this problem and it keeps our nightly backup from functioning. Are you completely up to date with Service Packs and hotfixes for Office? Does it still happen for someone with a clean profile? Sounds like your virusscanner then or other program that is used by the entire company that is preventing Outlook from closing properly. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD ...

problem installing CRM 3.0
Hello I just finished a successful installation of MS CRM 3.0. Did I say succesful ??? I received a positive message from the installation wizard telling me that MS CRM installed successfully. Nevertheless, when I open the homepage of MS CRM 3.0 I do not have the SALES and MARKETING module. Only the SERVICES and MY WORKPLACE modules are available on the left side of the page !! Please advise... If you logged in with the user that installed CRM, go to Settings, Users and uncheck Restricted User Access. "Guchy" <Guchy@discussions.microsoft.com> wrote in message news:8E1...

Trendlines in graphs
Hi all! I have an interesting question. I am ploting a graph where the X axis is by months and the Y axis is the number of parts produced. Some months we do not produce any parts; therefore, the Y axis is "0" at that time. When I put a trend line on the chart, it sees the "0" as a computable data point. A line is then generated but is off from the true trend. The true trend can be generated if I remove all the non producing months. Is there a way to specify specific groups of cells in a column that you want to use in a trendline analysis--sort of hop scotch ...

Add Trendline to Pivot Table Graph
I have a list of survey responses and I have created a Pivot Table with Month as the Row Heading and Count of Respondent as Data. I have then created a Pivot Table chart. I want to show a trendline but when I select the columns and right click for Trendline, as I would normally, the option is greyed out. How do I create a trendline for the pivot table category columns? If Add Trendline is disabled with a pivot chart (I don't know, I rarely make pivot charts), you could plot the data in a regular chart, then add your trendline. Pivot Tables, Pivot Charts, and Real Charts...

help with lookup #3
I have a large table with a list of items and their different prices from different suppliers. I am looking for a function to look at each item in a row, define the cheapest supplier, the column and then return the supplier name in another sheet. I have tried Vlookup and Hlookup but I can get it to work can anyone help. Item Supplier 1 Supplier 2 Supplier 3 Crisps 10 15 11 Juice 30 20 19 Beer 15 25 27 So at the end of each row I would have a cell showing che...

Importing from Word #3
I am trying to import a faily large document from Word, and want to retain the original formating - specifically the margin settings. When using the 'Import from Word' facility much narrower margins are set no matter what I do. I have also tried pasting the whole document from the clipboard to a pre-drawn text box, and letting autoflow produce new text boxes, but this removes much of the original formating. Any ideas New, from the Publications for Print menu select Import Word Document, select one of the templates, insert the Word Document. The document will come in with an on...

Setting Wall Color in 3-D charts
When setting the wall color in a 3-D chart I have encountered a problem with the side wall and floor. They appear to have a embedded shawdow set. Is there anyway to remove the shadow so the walls and floor will all reflect the same color as Excel 2003 allowed? I don't believe you can change the side wall and floor colors. Would you consider changing to a regular column chart instead? A normal column chart might work better because it's generally thought that 3-D effects can misrepresent the visual interpretation of the underlying data. -- John Mansfield http://www.cellmat...

weighted trendline
Can anyone tell me if it possible to weight a linear trendline in excel? Like a calibration line where you can weight by 1/x where x is the variance at each point on the line. Hi, I don't believe that can be done with anything built into the trendline chart feature, but you can use the trendline formula in the spreadsheet and then apply your weighting to it. Then plot that rather than the built-in trendline. You can get the formula of the trendline from the chart by choosing to display it or in the spreadsheet by using the LINEST function or the related SLOPE and INTERCEPT func...

Increaseing Precision in polynomial trendline equations
How can I increase the precision in Excel's "Display Equation" option for trendlines? I am trying to fit a polynomial regression (trendline) to a X, Y scatterplot I have created. Excel's built-in trendline function shows a good fit (R2=0.9999) for a 6th order polynomial, however, If I re-plot the data using the coefficients from the "Display equation" option, the data diverge significantly. Apparently, for high order polynomials, you need to have a high level of precision in your coefficients (many decimal places accurate) in order to actually re-plot the same ...

Trendline
How can I create multiple trendlines on one graph i.e I have a graph with 5 years data and I want to show a linear trendline for each year on the same line? You need each year plotted as a separate data series, then follow Mike's advise best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Carlo" <Carlo@discussions.microsoft.com> wrote in message news:3F847B4B-9C32-464A-82F9-001A340A67FB@microsoft.com... > How can I create multiple trendlines on one graph i.e I have a graph with > 5 > years data and I wa...

Disabled macros #3
Our company just upgraded to Windows XP and running Excel 2000. Yesterday I wrote some new macros and saved them, today when I try t run them I get the message: "The macros in this project are disabled." I cannot find when or how to enable them. Can anyone help? -- Message posted from http://www.ExcelForum.com Hi MileHigh, > "The macros in this project are disabled." > I cannot find when or how to enable them. Can anyone help?? > Excel is set to high macro security. Set it to medium so you can choose whether to enable them or not: Tools, Macros, securi...

Missing Email #3
Hoping someone can help. Here is my problem - I loaded SBS2003 from scratch added my users, everything was setup properly on that end - I configured the pop3 connector to get the email from the server and deliver it to the clients - a problem with how I had the recipient policies setup prevented the mail from arriving at the users mailboxes however (see problem 1 below) - detailed logging shows me that messages were found on the ISP's server, downloaded to our exchange 2003 server then deleted off of the ISP - detailed logging also shows though that the server was not able to route...

Econnect failing after upgrade to 8.0.3
We had been using 8.0.0.0, but after talking with support about and issue.. we were told to upgrade to the newest version. Now I'm getting the below message and it finally erros out. Has anyone else had this problem? Please help The adapter failed to transmit message going to send port "data source=172.16.21.209;initial catalog=ABC;integrated security=SSPI;persist security info=False;packet size=4096". It will be retransmitted after the retry interval specified for this Send Port. Details:" MessageID:00078fb1-84de-4073-aa81-897804607b5c System.Data.SqlClient.SqlExcepti...

Macro Help #3
Hello, Could someone please help me with a macro that will insert a row between Groups of Numbers. I would like to be able to insert a row between >=10000 and <=19999, >=20000 and <=29999, >=30000 and <=39999, >=40000 and <=49999, >=50000 and<=59999 and so on upto 100000. I export this from Quickbooks and have to insert rows manually Thanks in advance for any help Vender Parts UPC Item Description QTY 11003 15002 17000 18000 19000 19999 20000 21000 25000 26000 26010 see my reply in your request of same subject in GQ?? "Mike" wrote:...

Trendlines
Does anyone know how to choose the correct trendline for a data set? I have plotted a data set in Excel 2007 (located at the following link) http://www.mediafire.com/?sharekey=a6c026861bdd9f9ad2db6fb9a8902bda, but I don't know how to choose the correct trendline from the options Excel provides. The 'correct' trendline depends on the data. If I weight 2 bags of sugar, then 4 bags, then 5 I would expect the trend to be linear. But if I measured the area of circles of different radii, then I would need to use a second order polynomial with zero intercept. And so on. What is you da...

Sending external email from CRM 3.0 #3
We cannot send external email from CRM, only internally. The Exchange and CRM servers are on different computers. I followed the troubleshooting steps outlined in KB article 939402 but still same result... Any ideas? ...

Support #3
Does any one know that Microsoft offered a 1 year plan for upgrades and unlimited support and after paying for it 2 months later they they change the plan and say you are no longer elegable and have to pay for phone support. -- rvsupplie ...

If Statements #3
Hi all I am new to this, can someone, please help me with this. I wont to now how to evaluate two or more logical conditions if they are true or false using if functions for example. If total purchases in A2 are greater than $7000.00 deduct 15% from those purchases and if purchases are greater than $5000.00 deduct 10% of purchases otherwise purchases stay the same. How would I do this if funtion, Please, maybe use a or somehow? Cheers Mark -- burkey ------------------------------------------------------------------------ burkey's Profile: http://www.excelforum.com/member.php?actio...

CRM 3.0 Email Router event error
I have upgraded 1.2 to 3.0 successfully apart from a rapidly reoccuring event in the application log from the MSCRMExRouterService: Message: [Subject:'Exchange Router Service Alert on SBSSERVER01' From:'' To:'"crmmail@company.local" <crmmail@company.local>' CC:''] in mailbox: [CRMServerUrl:'http://SBSSERVER01/' EmailServer:'SBSSERVER01' EmailAccount:'crmmail' ForceReDelivery:'True'] has no single attachment. I am running the router service as local system. I also have the crm web site on port 5555 which doesn'...