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
953 Views

Similar Articles

[PageSpeed] 41

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:

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 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...

Three Variable Correlation
Hi. I was wondering if it was possible to work out the correlation between three variables in excel, not just two. My data looks like this (this is just an example, with random values): A B C 15,283,741,924 22,891,175,671 182,489,383 12,450,453,827 25,604,121,237 458,039,634 25,017,912,202 10,105,478,615 408,312,030 15,608,597,275 27,352,700,295 71,393,406 6,293,126,663 33,730,623,537 699,182,754 15,665,042,562 25,952,123,256 76,238,591 11,565,224,759 30,426,279,924 44,724,721 8,716,353,737 33,395,986,265 135,495,541 ...

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. ...

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 ...

Excel should allow correlations of data so chart markers match up
When creating complex charts with columns and markers all markers show up in fromt of one column instead of being matched with the correct column. If information could be correlated the markers could be line up with the correct column which would make reading the chart easier! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsread...

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, ...)...

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 ...

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/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...

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 ...

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...

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:...

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...

Excel for dummies
I have the following two arrays A B 0.656762726 0.502636772 0.607062936 0.358819486 0.176421847 0.177496945 0.850282971 0.408820642 0.840389619 0.238850111 0.677780042 0.95826463 0.689123518 0.32826283 0.09339563 0.080583459 0.449812814 0.62147784 The covariance is 0.025759373 and the standard deviations are a: 0.269721719, b: 0.263401137. I would expect the correlation to be Cov(a,b) / (Stdev(a) * Stdev(b)) = 0.362578234 However, Excel calculates the correlation as 0.407900513. Please educate me. Thanks, Schiz Note that 0.407900513/0.362578234 = 1.125 And 9/8 = 1.125 Now look at the...

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 ...

Statistical Analysis: Pass/Fail Correlation
I have an Excel spreadsheet (.xlsx) that I want to use to determine correlation. There are 30 columns and 690 rows (1 row of “Labels”, and 689 rows of data). The 2nd column is Pass (“1”) or Fail (“0”) data. My goal is to determine what factors most strongly influence the part to Pass, and which most strongly influence the part to Fail. I am no wizard at statistical analysis, so I would appreciate any help I can get. I can make the spreadsheet available to you so you can play with it. There is no proprietary data in the spreadsheet (everything has been coded with a numerical...

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 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...

Three Variable Correlation
Hi. I was wondering if it was possible to work out the correlation between three sets of variables in excel, and not just two. The data looks like this (example values): 15,283,741,924 22,891,175,671 182,489,383 12,450,453,827 25,604,121,237 458,039,634 25,017,912,202 10,105,478,615 408,312,030 15,608,597,275 27,352,700,295 71,393,406 6,293,126,663 33,730,623,537 699,182,754 15,665,042,562 25,952,123,256 76,238,591 11,565,224,759 30,426,279,924 44,724,721 8,716,353,737 33,395,986,265 135,495,541 11,234,337,068 23,463,987,625 1,163,192,672 16,285,868,769 21,238,873,761 1,777...

data correlation and plotting irregular data
Im sure Im missing something but I have two issues regarding dat correlation. Is there a way to automatically scale the X axis for rea non arithmetic series data so I can then determine the trend correlation coefficient, equation to the line etc. for say the exampl below. Secondly, how do I determine Y from corresponding values of from the equation to the trend line rather than the interval value. quantity Income 680 7,092,102 1,423 4,549,599 1,535 8,279,940 2,010 7,304,693 2,086 6,596,971 2,142 6,586,680 2,882 7,327,627 3,428 8,520,046 3,496 8,824,036 4,073 14,400,000 4,576 12,208,911 5,52...

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...

Fourier Analysis & Cross Correlation
Hello, I am trying to complete my thesis and have run into a bit of a snag and am desperately hoping to find some assistance. My thesis involves a cross correlation and Fourier analysis of river stage, discharge and precipitation data. I, however, am not able to figure out how to run the data in excel. I have run it on a linex system but was not able to generate the decent graphs and am now trying run the data on excel but cannot figure out how to do it. Any assistance is greatly appreciated. Thank you very much. Thomas, Are you able to generate your numbers using Linux system? If...

Correlation coefficient for log plot..
Hi All I posted on 3 August ("Growth function falls over..") about LOGEST's inability to handle missing data. My data [x=120, 150, 200, 235; y=100, 75.5, 61.2, 50.1) plots on a log/lin plot (x-y scattergraph). Jerry Lewis came up with a neat solution using SLOPE and INTERCEPT to replace =INDEX(LOGEST, etc..), which works fine, but I now have a similar problem with the correlation coefficient, r. The only way I have found for (presumed) accurate calculation of r is to use LOGEST entered as an array formula to get the extra stats, including r squared. This agrees exactly with the...

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? - ...