Comparing 2 series of data

Hello,
I have a question regarding scatter (XY) charts on Excel 
2000.  I have 2 series of data that I want to compare 
with one another.  I have plotted both series of data on 
a scatter (XY) chart in order to compare the 2 series in 
terms of the relationship between variables, X and Y.  In 
addition, I have included a 2nd order polynomial 
trendline for each series so that the graph contains 2 
trendlines (one for each data series).  I want to know if 
there's a way for me to compare these 2 series of data 
(possibly using slopes of the trendlines) and obtain p 
values and confidence intervals to know if there is a 
significant difference between the 2.  

Another problem I'm having:  I have a series of data and 
I want to know if 2 variables within that series 
correlate with one another.  In other words, I want to 
know if Y increases/decreases as X increases/decreases 
and vice versa.  I have plotted out the data and added a 
linear trendline.  I was thinking that the slope of the 
trendline may indicate a correlation, but I don't know 
how to determine if the correlation is significant.
Any help would be greatly appreciated.  Thank you very 
much.

-2
7/30/2003 3:15:58 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
526 Views

Similar Articles

[PageSpeed] 21

Tobi -

Several years ago I put together a workbook that did this type of test. 
  You pasted two sets of points into the worksheet, it computed the 
fitting coefficients (I only used a first order linear fit).  Then it 
performed tests on these coefficients.  If memory serves, it was a T 
test on slope, a T test on intercept, and an F test on a more 
complicated multiple regression to test for coincidence of the lines.

You should consult a statistics textbook for the appropriate formulas. 
Tests for equal slope and intercept, and tests for coincidence of two 
lines, should be covered in a decent text.

I think if you have the same number of points, and the X values all 
match up, you could do a paired T test on the Y values.  This ignores 
the issues inherent in fitting data to arbitrary polynomials, and is 
mathematically easier.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tobi Wong wrote:
> Hello,
> I have a question regarding scatter (XY) charts on Excel 
> 2000.  I have 2 series of data that I want to compare 
> with one another.  I have plotted both series of data on 
> a scatter (XY) chart in order to compare the 2 series in 
> terms of the relationship between variables, X and Y.  In 
> addition, I have included a 2nd order polynomial 
> trendline for each series so that the graph contains 2 
> trendlines (one for each data series).  I want to know if 
> there's a way for me to compare these 2 series of data 
> (possibly using slopes of the trendlines) and obtain p 
> values and confidence intervals to know if there is a 
> significant difference between the 2.  
> 
> Another problem I'm having:  I have a series of data and 
> I want to know if 2 variables within that series 
> correlate with one another.  In other words, I want to 
> know if Y increases/decreases as X increases/decreases 
> and vice versa.  I have plotted out the data and added a 
> linear trendline.  I was thinking that the slope of the 
> trendline may indicate a correlation, but I don't know 
> how to determine if the correlation is significant.
> Any help would be greatly appreciated.  Thank you very 
> much.
> 

-1
jonpeltier (303)
8/1/2003 1:23:43 AM
Reply:

Similar Artilces:

Viewing IMAP messages #2
I connect to a Linux IMAP server remotely for my emails. When I click on an email in Outlook P once the headers have been updated the system seems to download the email and the attachement. All I want to do is read the message before I download the attachment - Is this possible? Its a bit of a problem if an email has a 3mb attachement and all I want to do is read the mail content before deciding to download the attachment - especially a pain if I am CC into some mail. Regards Andy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Ve...

Sumproduct #2
Hi all! I am trying to use Sumproduct with dates that come off an SQL databas and are in the format of 02/12/2003 15:30. This is forcing me to use the following formula =SUMPRODUCT((Data!$E$2:$E$3893>G$3)*(Data!$E$2:$E$3893<G$2)*(Data!$C$2:$C$3893=$A4)*(Data!$G$2:$G$3893)) where $G3 is 02/12/2003 00:01 and $G2 is 02/12/2003 23:59 Is there any way I can just look at the date only rather than the time as well, so that anyone can enter a date from, and a date to In anticipatio ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and p...

pasting into merged cells #2
Every time I try to copy and paste data into a merged cell I get error messages about the cells not being the same size. Even if I try to just paste values only. Is there any workaround or fix for this??? Dennis Try pasting into the formula bar. It's a pain, but it works. HTH Carole >-----Original Message----- >Every time I try to copy and paste data into a merged cell I get error >messages about the cells not being the same size. Even if I try to just >paste values only. Is there any workaround or fix for this??? > >Dennis > > >. > See my res...

Work out overtime hours for individuals #2
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

Split Forms
Is it at all possible to hide the Data Sheet portion of a split form at run time in 2007? The only solution I have found is to play with the SplitFormSize property, but I am afraid that it does not support different display sizes. Any suggestions ? Thanks ...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

MS Office VBA Automation Specialist #2
Hi there, is there such a certificate? MS Office VBA Automation Specialist, I tried googling it on microsoft website and couldn't find any info. I just passed the excel 2k expert exam and I want to take the excel instructor exam, I haven't however been an instructor. is it possible to take the exam without an instructor experience? last but not least, I am thinking about putting together some excel webcasts for beginners for free. I have always liked to learn by audio/visual versus reading so I am thinking about implementing this... here is the link for the first test webcast ...

Making cards --2 to a 8 1/2 x 11 page
I want to make cards to send out for memorials, etc. I want 2 cards to a page, top folded 5 1/2 x 8 1/2. What happened to the old Picture It! Publisher 2001? Do you want to make these cards in Publisher? If you do, look at page setup, folded card, tent card, width 4.25, height 5.5. Publisher will say it will print two pages per sheet -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "A Holt" <A Holt@discussions.microsoft.com> wrote in message news:F5F75EEF-B186-4960-9AEB-AF1C678492B7@microsoft.com... >I want t...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Download email #2
How to download email from Microsoft exchange 2000 to Outlook Express?? without leave a copy at Exchange server. Outlook Express cannot download mail from Exchange. Why are you trying to do so without leaving a copy of the mail on Exchange? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Edi asked: | How to download email from Microsoft exchange 2000 to | Outlook E...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

Insert Data From Multiple Worksheets Into One (With Criteria)
Ok, I have created a sales report list that tracks what customers we are contacting, how we are contacting them, if they are a new or existing customer and if we sent a quote, along with some other details. There is a worksheet the salesmen fills out for each day of the month (its linked to a calendar) and that all goes into a "dashboard" that shows the breakdown with graphs. Now what i would like to do is have another worksheet list all the customers that we sent quotes to so they can mark if the quote was won or lost , state sales projections and give reasons why won ...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

joining data points on graph
I am merging two graphs and one has 23 data points, the other has 9. The category axis will have 23 data points but the 9 points are spread out, i.e. they may be at points 1, 3, 7, 15 etc of the 23-point scale. At the moment the graph will only join points that are beside each other; how do I get them to connect to the points that have spaces between them? minihana wrote on Fri, 10 Feb 2006 11:29:04 -0800: m> I am merging two graphs and one has 23 data points, the m> other has 9. The category axis will have 23 data points but m> the 9 points are spread out, i.e. they may be...

Dynamic data label placement for graphs
I've read the article KB179199 on how to create custom data labels for charts and it's good, however doesn't meet all my requirements. Is there a way to have the data labels automatically move to a desired location relative to the graph? For instance, I'm using 50 bar graphs per work book and would like each data label to be just above their respective bar. TIA Hafeez I looked up article 179199 and got this: BUG: Grid Based on .QPR File Appears Blank in Visual FoxPro 5.0 which probably isn't the one you meant. By above the bar, are they horizontal bars and you ...

Field Chooser #2
Guys-is it possible that some of the fields in Outlook 2000 (from Field Chooser) can simply disappear or could the problem be that somebody else is accessing my mailbox? It happened to me that the From field and Subject field have simply disappered twice without my action? Could it be a bug or something similar? Is it possible those fields are already in the table view? I've been spooked by that one before. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

authentication #2
I seem to keep getting a password and ID prompt in outlook for my HTTP Hotmail account even though I am entering the correct password and ID because i can get to this account over the internet. remember my password is also checked in tools\account\properties etc. Version of Outlook? Does it work if you log into the site in a browser? -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Exchange Messaging Outlook ...

MsgBox #2
I have been search the web for hours looking for example spreadsheets using MsgBox to no avail. Can someone direct me in the right direction? What I am looking to do is ask a question and if the answer is "yes" do 1 thing and if "no" do something else. "Ed Davis" <ed.davis1@verizon.net> wrote in message news:4EBF4D2F-7C3F-4BAB-8366-A1C744276744@microsoft.com... >I have been search the web for hours looking for example spreadsheets using >MsgBox to no avail. > Can someone direct me in the right direction? > Hi Look at this: Sub test()...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

Outlook 2003 to OWA? #2
Can Outlook 2003 synchronize with OWA? I only have OWA access to my clients exchange server. I've done it with Entourage, but Outlook is much less intuitive... or maybe it cannot be done? Correct. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, stumpy asked: | Can Outlook 2003 synchronize with OWA? I only have OWA | access to my clients exchange server. I&#...

Merge data by macro
Hi all, I got data in column A and B as show below. Row A B ......col 1 ID DATA ...headings 2 XY01 Record 3 Time 4 Left 5 XY02 Time 6 Right 7 XY03 System 8 Record 9 Time 10 Left I need macro which should merge column B data and put result in column C as shown below Row A C ......col 1 ID DATA ...headings 2 XY01 Record Time Left 3 4 5 XY02 Time Right 6 7 XY03 System Record Ti...

how to merge data from multiple columns to one column #2
I have first, middle, and last names in A, B, and C. I want to merge everything into A. Ho do I do that? In column D use the formula =A1&" "&B1&" "&C1 The " " would put in your spaces in between the names. Then go to column D & do Edit Copy, move to column A & do Edit Paste. Then you should be able to delete columns B, C & D. Hope that helps! >-----Original Message----- >I have first, middle, and last names in A, B, and C. I want to merge >everything into A. Ho do I do that? >. > =CONCATENATE (text1,text2,....

Grouping Same Data in column
Hi Guys, Using: Excel 2000 Issue: Is there a way to create groupings based on same values down particular column. Example: I have phone numbers listed down column L. But some of th numbers appear more than once. Can I automatically create groups base on the same phone numbers so it would be easier on the eye to follow. Hope this makes sense. TIA Jonas :cool -- Message posted from http://www.ExcelForum.com You could sort the list by the phone numbers, and use conditional formatting to highlight the rows that contain a duplicate phone number: Select a cell in the list Choose Dat...