How do I use the "Correlation" dialog box with multiple ranges?

I'm using data in an Excel 2003 in a workbook, trying to see if there is any 
correlation between specific offices in which people work and the length of 
time they have been employed, their age the amount of sickleave taken and/or 
the amount of sickleave balance they have. I'm attempting to use Data 
Analysis and the Correlation dialog box. Although the dialog description 
tells me that I can use multiple comparison ranges, I don't see how to do it. 
Thanks for any help.
0
Paulf6 (1)
4/3/2005 5:29:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
774 Views

Similar Articles

[PageSpeed] 42

Paulf6  -

Arrange your data in list (database) format, i.e., names in the top row 
(Time Employed, Age, ...) in adjacent columns. Subsequent rows have values 
for each person. Choose Tools | Data Analysis | Correlation. Select the 
entire list range as the "Input Range."

"Specific offices" is a categorical variable, not numerical, so it should 
not be included in the Correlation analysis.

I don't know what you mean by "multiple comparison ranges." I cannot find 
that phrase in the dialog box or in Excel's Help.

-  Mike
www.mikemiddleton.com

"Paulf6" <Paulf6@discussions.microsoft.com> wrote in message 
news:7F6A6DCD-3252-4ED7-BD2A-4D7C7610B419@microsoft.com...
I'm using data in an Excel 2003 in a workbook, trying to see if there is any 
correlation between specific offices in which people work and the length of 
time they have been employed, their age the amount of sickleave taken and/or 
the amount of sickleave balance they have. I'm attempting to use Data 
Analysis and the Correlation dialog box. Although the dialog description 
tells me that I can use multiple comparison ranges, I don't see how to do 
it. Thanks for any help. 


0
middleton (61)
4/4/2005 8:14:16 PM
Reply:

Similar Artilces:

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

In Regression Analysis, How do you get a Correlation Matrix to be.
How do you get the Correlation Matrix for your variables to appear on the output screen? tjp32 - > How do you get the Correlation Matrix for your variables to appear on the > output screen? < Tools | Data Analysis | Correlation - Mike www.mikemiddleton.com ...

Timesheet Review
We have installed SP2 and MS CU Aug09, and now 60% of my Timesheet Review jobs are failing. The error I get the following error. Does anyone have any experience with this issue? Error summary/areas: There has been an error processing a timesheet for which you are/were responsible. Check your approvals list to locate the offending timesheet if one exists. The error condidtion may be related to the fact that Timesheets are a state driven feature; therefore, the allowable values for processing are dependent upon the existing value which for this transaction was 3. Refer to the docume...

Correlation statistic in Excel
Hello, Does anybody know which type of correlation is used by Excel's Data Analysis tool? Is it a Pearson correlation? Thanks for the help! ...

Correlation
I have a table of data as below. There are more Customers, and more dates' worth of data, but they won't fit in this window. For a number of our customers, we change delivery routes during the week in order to optimize our shipping capacity. Basically, I am trying to see for each delivery route, on a given day, how many of the same customers are on the same route. In other words, when one customer changes from one route to another over a date range, do other customers switch to the same routes on the same days? Cust # Rt# 8/27/2005 8/29/2005 8/30/2005 8/31/2005 a 1069 ...

array formula for max of Pearson correlation
HI, I=B4d like to "compact" the following column in a single cell value using array formulas, if that is possible. The columa A is filled with results of a certain function, and column B has the Pearson correlation of that column in this way. b1: =3Dpearson(a1:a5,a2:a6) b2: =3D pearson(a1:a5,a3:a7) .. .. b100=3Dpearson(a1:a5,a101:a105) Then, what I really need is the max of those b cells, so c1=3Dmax(b1:b100). The question is, can I avoid the creation of column b, with something like : c1=3D{max(pearson(a1.....))}, if it is possible I can=B4t find the right sintax... Thanks ...

Correlation between 2 or more variables
I have the following information for total costs, total revenue,# of orders, #of units as well as the information as what constitutes the total costs. I would like to know how would I go determining if I changed total costs by varying one the the costs that affects it for example training costs, how that would affect # of orders. I tried regression analysis using Excel but the output did not make any sense "gpadmani@yahoo.com" wrote: > > I have the following information for total costs, total revenue,# of orders, > #of units as well as the information as what co...

graph to show correlation btwn two data sets of different ranges.
I have variables a,b,&c for items 1-5. The range for these variables is 100-1000. There is a fourth varibale (d) that ranges from 1-12. I want to show in 1 graph that changes in variables a,b,&c are directly correlated to variable d. Here's what I am thinking: 1. Create a column graph for variables a,b,&c with the y axis label listed on the left. 2. Add a line graph of variable d with the y axis label on the right. Problem: - I have no idea how to add the line graph over the bar graphs. - I can't for the life of me figure out how to move the y axis label to the right side...

Is there an addin for displaying correlation matrices as contour p
Chrissy Wissy - It's better to ask your question in the message body, not just in the subject (which was truncated by my news reader). Please provide more information or an example of what you want. Excel's Correlation tool (part of the Analysis ToolPak) produces a "matrix," i.e., a two-way table, of all pairwise correlations for multiple variables. Excel's 3-D Surface, Wireframe 3-D Surface, Contour, and Wireframe Contour chart types produce something like a contour plot. But I've never heard of combining these two features. What do you have in mind? - ...

Correlation tool says Input range must be contiguous reference.
Trying to use Data Analysis > Correlation tool, but keeps saying "Input range must be a contiguous reference." CORREL() functions works Ok on same range. What's happening? HermH - The only obvious way I can get that error message is to use a non-contiguous range for the Input Range. How is your data organized, what are you specifying for the Input Range, and what version of Excel are you using? - Mike http://www.MikeMiddleton.com "HermH" <HermH@discussions.microsoft.com> wrote in message news:40504FF5-7DE0-48B5-BE9C-501A1EE9ECB2@mi...

Correlations, how do I graph one?
I have data of my weekly pay and hours worked. I want to show the correlation between the two. When I do a linear regression it doesn't work, and I don't understand why the correl function is for the range of each data set. For example: 5/1 $100 5 hours 5/15 $200 7 hours 5/30 $50 3 hours etc. I have the money and hours graphed on a charted over time, on two separate axis. now how do I show the correlation between them on this chart? I tried to use the CORREL function to do somthing like this : CORREL (money1:money3,hours1:hours3) but the amount comes out as a really small decimal...

Correlation Slope
hi there i have two sets of data that i correlated, and now i want to show it in graph form. i used the the XY scatter graph to plot the two sets of data, but how do i show the correlation line / slope? thanks for helping a rookie best regards gerald Gerald If you Right click on a point in the graph you should get an option to Add a Trendline Good Luck Mark Graesse ----- Gerald wrote: ---- hi there i have two sets of data that i correlated, and now i want to show it in graph form. i used the the XY scatter graph to plot the two sets of data, but how do i sh...

how do I do first order serial correlation in Excel 2007
fIRST ORDER SERIAL CORRELATION IN excel 2007 wHAT IS THE COMMAND? Do you mean a linear fit? Look at functions SLOPE and INTERCEPT; and LINEST if you want lots of detailed stats. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "RICHIE" <RICHIE@discussions.microsoft.com> wrote in message news:E83B2C7A-825A-451F-8C1D-6BDFBDEE1D33@microsoft.com... > fIRST ORDER SERIAL CORRELATION IN excel 2007 wHAT IS THE COMMAND? Bernard, Thanks for asking that question! -- Thanks, Shane Devenshire "Bernard Liengme&...

Correlation/Multiple Regression
I'm trying to analyze numerous columns of data using both the correlation and regression functions under Tools--> Data Analysis (toolpak add-in), but I keep getting an error message telling me that some data is in non-numeric format. All of my data points are numerical (ratios, percentages, and currency), so I don't understand why Excel won't recognize the data. Any suggestions? Though they are numerical, they may not be recognized as numbers.. How is the ratio written in? Also make sure the numbers, when you activate the cell, don't have the signs in the actual value. i...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...

Correlation?
I'm just starting my work on this so please go easy on me. I have a bunch of data in front of me that will help me generate some answers in five different categories. I have the ability to sort the data geographically, gender, age etc. and was wondering if anyone could recommend or help me determine/test if there is a correlation between certain groups? e.g. females in different geographic areas? What would consist of a normal correlation test? HotRod - If you want to describe relationships among categorical data, you can use a Pivot Table to get summary counts for two charact...

RANK and CORRELATION
Greetings. I realize this is a simple statistical question, but I don't have a statistics package (or any knowledge of statistics), but I am using EXCEL 2003. I would deeply appreciate your help because I can't figure out on my own how to do this in EXCEL. I have a column of numbers in random order (5, 33, 4, 76, 32, 46, etc.). The lower numbers are "better" than the higher numbers (they are scores on a test; higher numbers mean more mistakes). I have another column of numbers also in random order (not ranked). The lower numbers are "better" than the hi...

Correlation Charting
How do I get two colums of data to chart next to each other when their vales are very different, so the Y axis is only charting the bigger value. Ex. Sales values in weeks 1 - 8 range from 50,000 - 100,000 and hours worked range from 30 -50. I want to show the correlation on the line chart, but it puts the hours worked data as a straight line at the bottom of the chart because the values a re so low compared to the sales data. Hi, Select one of the series and on the Format dialog, CTRL+1, Axis tab set the series to the secondary axis. Cheers Andy -- Andy Pope, Microsoft MVP - Exc...

Correlation Query
I am looking to compute correlation. I have a table with x values and y values and would like to run something similar to the CORREL function in EXCEL. Does anyone have a query like that which works? AJ wrote: > I am looking to compute correlation. I have a table with x values and y > values and would like to run something similar to the CORREL function in > EXCEL. > Does anyone have a query like that which works? I tried the following with the numbers from the example in Excel 97 help for CORREL: tblXYData ID AutoNumber X Double Y Double ID X Y 1 3 9 2 2 7 3 4 12 4 5 15 ...

spearman correlation coefficient
does anybody have an example of spearman correlation coefficent calculating EXCEL file with the siginifcant values at 90%? A quick Google search (using "Spearman correlation coefficient Excel") brings up several hundred possibilities, including Calculating Correlation with the Excel Spreadsheet Program http://www.mnstate.edu/wasson/ed602calccorr.htm In article <646936BA-D224-456D-938F-37F40F40AD3F@microsoft.com>, =?Utf-8?B?TWFyY2VsIExhYm9udMOp?= <Marcel Labonté@discussions.microsoft.com> wrote: > does anybody have an example of spearman correl...

How do I set up a correlation table?
I am working with survey results. I need a correlation table that represents the data taken. excel has the CORREL function rh555 - > I am working with survey results. I need a correlation table that > represents the data taken. < A) CORREL worksheet function B) Correlation tool of the Analysis ToolPak add-in - Mike www.mikemiddleton.com ...

Data correlation problem
I hope I can write this question so it makes sense... I have an item database, about 30,000 items. It is realy only about 50 items, laptop batteries and adapters, but most of them works with a lo of laptops, so the same items are listed over and over again, bu generally not right next to each other. For instance, a Compaq Presari 1200 might take two batteries, an AC Adapter, and a Car Adapter, s that's 4 part numbers: P/N B-5333 B-5333/LI AC-C10 DK-C10 Each day I receive an "eta" file, a list of part numbers that are ou of stock in one column, with the date that they are backo...

Correlation coefficient (r)
Hi folks, This question is similar to Elizabeth Brown's, but with a slight twist. I am also looking to calculate r ..from a exponential function plotted as a log-lin scatter graph: I posted last week (5 August) about a way of calculating the correlation coefficient from a log-lin plot that is tolerant of deleted/mssing data, but the proposed solution (CORREL array-entered) doesn't seem to work. Unless I have overlooked something? To restate, I have a Log-lin plot based on data in columns A and C. Column C contains the y-values. Columns A and B just contain data (A1:A4 = 100,150,200,...

multiple correlations with one click?
I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. I know what a correlation is but I have no idea what you want. Define "correlation" in your context. IOW, what do you want to see as the result? HTH Otto "bondjel" <bondjel@discussions.microsoft.com> wrote in message news:...

XL 2000 Correlation Matrix
How to do a correlation matrix in Excel for a large data set? I can only find a correlation function scaler for two vectors, but I want one for a whole matrix. Any suggestions or helpful insight would be appreciated. George George Wilson - > How to do a correlation matrix in Excel for a large data set? I can only find a correlation function scaler for two vectors, but I want one for a whole matrix. Any suggestions or helpful insight would be appreciated. < Tools | Data Analysis | Correlation The Correlation tool of the Analysis ToolPak will compute pairwise correlation coe...