#### Correlation - Which Analytical Function to Use

```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
a	1081	1
a	1088
a	1090		1	1
a	3271
b	1003	1
b	1076		1
b	1089			1
b	1101
c	1069
c	1071
c	1081	1
c	1082
c	1090			1
d	958
d	1069
d	1070
d	1072
d	1080			1
d	1082
e	1069

```
 0
Pasko1 (6)
10/6/2005 8:09:03 PM
excel.misc 78881 articles. 5 followers.

2 Replies
546 Views

Similar Articles

[PageSpeed] 40

```Pasko1,
I'm pretty unclear about precisely what you are trying to do, however,
there are two suggestions I would make. One fairly easy to learn and
use, the second more complex but more powerful.
First suggestion is to use the Auto Filter. To do this, select your
entire range of data, then go to DATA>FILTER>AUTOFILTER. This will put
dropdown arrows into each of your column headings, with which you can
filter you data.
If for instance, one of your column headings is a date (as it appears
to be) and the column under that contains route numbers, you can filter
for a particular route number and only those rows will remain visable.
Then you can use the drop down to select "All" and your entire data
will be visable once more.
The second method involves using a pivot table, very powerful, somewhat
complex. If you go to DATA>PIVOT TABLE and PIVOTCHART REPORT, a wizard
will be launched to help you develop the pivot table.
I encourage you to avail yourself of the help files to study the
advanced techniques for both of these built-in Excel features.
HTH

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4545
View this thread: http://www.excelforum.com/showthread.php?threadid=473923

```
 0
10/6/2005 11:41:49 PM
```I would think that if you had your data laid out like:

Cust#	Rt#	Date	       Qty
a	1069	08/27/2005	1
a	1069	08/29/2005	1
a	1069	08/31/2005	1
a	1081	08/27/2005	1
a	1081	08/29/2005	1
a	1088	08/27/2005	1
a	1088	08/30/2005	1
a	1088	08/31/2005	1
a	1090	08/29/2005	1
a	1090	08/30/2005	1
a	1090	08/31/2005	1
a	3271	08/27/2005	1

(I wasn't sure if the 1's were quantities or just placeholders meaning yes)

Then you could use Data|Filter|autofilter to review any date/route/customer.

If you think you want to try that, you could use a macro to rearrange the data
into that tabular form:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 4).Value _
= Array("Cust#", "Rt#", "Date", "Qty")
oRow = 1

With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
For iCol = 3 To .Cells(iRow, .Columns.Count).End(xlToLeft).Column
If IsEmpty(.Cells(iRow, iCol)) Then
'do nothing
Else
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pasko1 wrote:
>
> 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
> a       1081    1
> a       1088
> a       1090            1       1
> a       3271
> b       1003    1
> b       1076            1
> b       1089                    1
> b       1101
> c       1069
> c       1071
> c       1081    1
> c       1082
> c       1090                    1
> d       958
> d       1069
> d       1070
> d       1072
> d       1080                    1
> d       1082
> e       1069

--

Dave Peterson
```
 0
petersod (12004)
10/6/2005 11:54:52 PM
 Reply:

Similar Artilces:

Using different values for data labels
I am charting data, but would like to use different data as my labels. For example, I want to chart a value (say, \$100), but I want the data label to be a different value (say, 20% because my \$100 represents 20% of something not being charted). Is there a way to point the data labels to other values within my worksheet? Thanks, Try one of these Rob Bovey's XY Chartlabeler (www.appspro.com) works on other chart types as well or John Walkenbach's Chart Tools (www.j-walk.com). best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bruce Sk...

Apply a Function to a Button in a Form
I am trying to apply a function (wchich is contained in a module) to a button in a user input form that will read the value in once field of the form and convert that value to a different format in a different field in the form. Here is the function that I am trying to apply: Sub DegToDMS(ByVal L As Double, D As Integer, M As Integer, S As Double) ' ' Converts a decimal degree to Degrees, Minutes, and Seconds ' Seconds may contain up to 3 decimal places. ' e.g. 15.5 -> 15,30,0 ' D = Int(L) L = (L - D) * 60 M = Int(L) S = Val(Format((L - M) * 60, "#.###&q...

CHOOSE function
Hi, Is there a way of using the CHOOSE function if you have more than 29 values? AND Is there a way of avoiding typing in all of the 29 (or more) values into the formula box? Thank you Wouldn't VLOOKUP with a lookup table be better suited than a great whack = of values in one CHOOSE formula? Or with a list of sorted values in A1:A34 and a list of values in B1:B34 In D1 enter =3DLOOKUP(C1,A1:A34,B1:B34) Enter a lookup value in C1 Gord Dibben MS Excel MVP On Wed, 2 Jun 2010 10:47:55 -0700, amanda = <amanda@discussions.microsoft.com> wrote: >Hi,...

How can you count if the same word has been used in a Spreadsheet?
i.e. a standard spreadsheet has a list of different names (random) and I want to do a 'word count' as such e.g. how many times 'Donnelly' appears in the spreadsheet. Hi donners6, Assuming there are NO Names that you want to Find in Column "A", Insert this Formula in Cell "A1" :- =COUNTIF(B:IV,"=Donnelly") Hope this Helps. All the Best. Paul "=?Utf-8?B?ZG9ubmVyczY=?=" <donners6@discussions.microsoft.com> wrote in news:B33FE0A8-DEF9-403C-A64F-A6CFB5EFF294@microsoft.com: > i.e. a standard spreadsheet has a list of different...

More complex Frequency function
Hello, Can you please help me on this one? I have this table to wicj I apply the frequency function (=FREQUENCY(B7:B13,D7:D10) ), which is ok. Age # of People Bin Frequency 33 6 10 0 34 25 15 1 14 9 20 2 17 6 30 1 29 16 3 20 4 35 1 Now, I want to calculate the frequency based on the number if people I have in that age. For instance, the frequency of 15 should be 9 and not 1 (there are 9 persons with 14 yrs of age); the freq of 20 should be 10 and so on. How can I do it? Than you, ...

IF NOT or IS NOT function #2
I am trying to write a macro to display a message if a certain cell does not contain certain data. (I am working in Visual Basic) i.e If cell F1 = 100 then end If cell F1 is not 100 then display a message "please re-enter data" Can anyone please help You don't need a macro for that, Nick. Try this: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com Nick Wright" <NickWright@discussions.microsoft.com> wrote in message news:E8D66ABB-A1C4-4041-A754-...

Using Tab Control in Dialogs?
Hello, I want to use tab control in my dialog. I can add page to my tab control, but how I can edit tab contents? (I don't want to use property sheets.) I'm going to use dialog resource as tab page contents. Thanks in advance. Sadjad Fouladi wrote: > Hello, > I want to use tab control in my dialog. I can add page to my tab > control, but how I can edit tab contents? (I don't want to use property > sheets.) I'm going to use dialog resource as tab page contents. > > Thanks in advance. For each page, create a modeless dialog template and class. Turn off t...

need to use two queries
Hi I open Form2 by double click on the text2 useing subform. Query2 is record source for Form2 and subform. I need to use two queries, one for Form2 and second for subform. when I use two queries data not appear thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 ...

Restricting access by store to application using ExOLEDB
We are working with Lexis Nexis's Time Matters product and they require their synchronization agent to be a local administrator on the Exchange server and also an Exchange admin. It uses ExOLDDB and appears not to be very well documented. The application seems to access all mailboxes on the Exchange server and we'd like to restrict it to a specific store. I know this isn't the cleanest way, but we thought if we modified the store permissions for the account that executes the application with explicity DENY's. Unfortunately that didn't work. Any suggestions? Tha...

How can I access my contacts without using Outlook?
My Outlook won't open as it is missing an OMINT.dll file. How can I access my contact info? I've tried opening the PST but no other program will open it. What is the exact error message, the version of Outlook, and what you are trying to do when it happens. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives, Adrian <agnorman@yahoo.com> typed: | My Outlook won't open as it is missing an OMINT.dll file. | How can I access my contact info? I've tried opening the | PST but no other program wi...

I am using the COUNT function to determine the
number of data entries in a row. The number of entries vary from 40-70. The starting point of my range is constant, but the last entry changes, depending on the number of entries. Is there a way I can write the COUNT formula, so I do not have to change the second part of my range? Thanks for any help. Skip Hi Using CountA will count the number of non-empty cells in the range: =COUNTA(A1:A100) Regards, Per "Skip" <ssummer@live.com> skrev i meddelelsen news:a951619c-51d9-467f-a451-df73a628a102@e31g2000vbm.googlegroups.com... > number of data entrie...

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

Average function for letters
I am trying to compute a percentage for a range of A4:A24; the only values in the cells are either an "X" or it is left blank. If possible, I am trying to compute the percentage of the whole A4:A24 range where the "X"s will count as a yes and the blank field will count as a no, adding up all the X's (yes's) and counting them against the blank fields (no's) to get an percentage of how many X's there are compared to blanks Can anyone help please, Ive searched tutorials but im not sure this is possible Thanks EB -- ZWarren "ZWarren" <ZWar...

XL2000 use/save over a network problems
Is there any way of changing the default of when a file is opened the temporary file is saved in the same location. I understand that XL2000 works on the temporary file, if this is so then i would like to redirect that temp file to my local drive. ps this is due to the fact that the files i am using are very large >60mb. ...

Import CSV files using Macro
i have 41 csv files that i need to import into 41 sheets in Excel. the name of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz Well, one way would be to use the Data->Import External Data->New Database Query and select CSV files as the source database. Drawback: it's completey manual & you'll have to go through it 41 times Another way is to choose File->Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You ...

Problem with a Function
I had posted earlier some formulas I am trying to reduce to functions and didn't get any response. I guess it was too hard or maybe can't be done. Anyway, I'm taking a small piece of it and trying things on my own. In a cell I have the following function: =QBRushYds(1, 1) The first argument is week number corresponding to a sheet name and the second arguement is the position of the player corresponding to a cell on the sheet. The function returns an error (#Value): Here is the function definition: Public Function QBRushYds(W, Q) ' Calculates Points for R...

How to detect if function dialog is calling a custom function
I have a custom VBA function that I use in my spreadsheet. The problem is that when it is called using the function editor, every keystroke causes it to recalculate the function. Now, the function can sometimes take a long time to return because it is calling a database, causing the editor to lock up on every key stroke making it impossible to use the editor. A- is there a way to make it not calculate on each key press. -- or -- B- is there a way to determine if the function editor is calling the function so I can return dummy data until its called from an actual cell? ...

Use query results to copy files?
Hi, I have a query that returns results like: Name: ID: Picture ID File Name: Bob 1234 bobs_id_pic.jpg Tina 5678 tinas_id_pic.jpg .... Is there a way to use these results to (automatically) copy all the id_pics to another folder (assuming all ID pics are in the same folder)? Thanks! Yes, but it's not a trivial task. You would have to open a recordset on the query results and then use the file system object to do the copying. If there aren't too many, it will be quicker to do it manually. -Dorian "Gary" wrote: > Hi, I have a qu...

CString extension class causes proplems if it's used as an argument in sprintf(...)
Hi, I created my own CString class called CMyString which is derived form MFC's CString. Everything works fine except I use it as an argument in a sprintf(...) function or each other function which have a variable argument list. If I pass an instance of my class CMyString to sprintf(...) I have to cast it explicitly with (LPCTSTR) or (CString) to get the correct result. But if I pass an instance of CString I don't have to cast it. Do anybody know what's going on there? ThanX, Markus See my code below: ////////////////////////////////////////////////////////////////////////...

ROUND function syntax question (hopefully simple)
I want to round the results of the following formula to a single decimal place. Can anyone tell me the correct syntax to use the ROUND function? Putting all the commas and parentheses in the right place has always been my downfall where long, complicated Excel functions are concerned! The formula is: =IF(J7>0,D7/J7*100,"") One way: =IF(J7>0,ROUND(D7/J7*100,1),"") In article <1193928344.343925.50970@k79g2000hse.googlegroups.com>, Nick Xylas <nickxylas@wmconnect.com> wrote: > I want to round the results of the following formula to a single >...

Use workflow to send email with a url to the entity page in CRM4.0
We just upgraded to CRM4.0 and I want to start using workflow for several notifications. We have the email router installed and it is sending email from workflow. Here is the question: I have a workflow that checks to see if the status field is updated, if so, send an email to the record owner. the email should indicate what was changed and have a direct link to that record. How do you do the link to the page. I don't see this an option in the dynamic values list. If I need to construct this manually - I know the page I need to get to, but I don't know how to get the gu...

Using Double variable with a C++ dll
Hi, I am using a dll written in C++. One of the declarations is : (In a module) Public Declare Function S52_moveView Lib "libS52.dll" _ (ByVal horizontal As Double, ByVal vertical As Double) As Long (In the code of a form) Select Case KeyCode Case vbKeyUp S52_moveView 0#, 1# Case vbKeyDown S52_moveView 0#, -1# End Select When passing a negative value (with vbKeyDown), the program jams, with an overflow error, or with a division by zero error. I have no idea why this occcurs. Thank you for reading me, an...

Dynamics Advanced Analytical Cube Library rolling 4 week measure
Has anyone had any experience createing a rolling 4 week or rolling 13 week measure? I noticed that there are quite a few measures developed in the periodicity table, but can't quite understand how to add a new one. Thanks in advance for the help. "George Schimenti" wrote: > Has anyone had any experience createing a rolling 4 week or rolling 13 week > measure? I noticed that there are quite a few measures developed in the > periodicity table, but can't quite understand how to add a new one. Thanks > in advance for the help. Whoops, my mistake. No...

Difference between CTE and correlated query
Hi I am not very clear on what is the difference between the foll. Any input is appreciated. Common table expression (CTE) correlated subquery Is a query in the from clause same as one of the above? Is the main difference in the above the number of times the query is invoked for? CTE can be used for recursive queries. Correlated queries are for calculating a sub query that returns set of values and using those values each time on the outer query. A CTE is like a "local VIEW". It can reference other CTEs that were declared before it in the statement. A deriv...

Using function IF in WORD 2003
Why the following example does not work? Example: Bookmark "town" has value "Pasco" {IF town = Prague "Not true" "{IF town = Pasco "True" " "} "} The outcome of this function is "{IF town = Pasco " but I am expecting outcome "True". That means the word does not take into account another nested function IF. I am trying to use nested function "If" because I saw this example in Word 2003 help where nested If is used (I thought that it will work): {IF {MERGEFIELD MÄ›sto} = "Brno"...