I have a product list going down column A, Avg Sale Price List going down Column B, a Margin % List going down Column C, and a Correlation List going down Column D. Here is a small sample of my data.
Product Name
Avg Sale Price
Margin
Correlation
372 Grizzly Salmon Oil Total
48.99
35.31%
-0.423
[Code] ........
I am trying to turn a list into a correlation matrix.
Goes like this :
From :
1 1 1
1 2 0.6
1 3 0.7
2 2 1
2 3 0.9
3 3 1
To
1 2 3
1 1 0.5 0.6
2 e 1 0.9
3 e e 1
The first list is from worksheet 1, and im trying to create the matrix in worksheet 2. Both worksheet is in the same open workbook.
Here's the code I've been using
Sub button1_click()
Dim i As Integer
Dim j As Integer
Dim b As Integer
Dim a As Integer
Dim c As Integer
[Code] ........
Now the weird thing is, this worked on a smaller matrix before (31 x 31) but now it just would't work at all (85 x 85)
it keeps giving me the error run time 1004 : application defined or object defined error.
Here's the file : matrix.xlsx
I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.
Formula Test.xlsx
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
concerning the use of correlation in vba. What are my options when i want to calculate a correlation within vba. The variables are all still within vba.
Who knows what formula's i could use to keep everything within vba.
If you don't understand my question please ask so i can try to make it more clear.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedThe function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:
[Code] ......
I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?
Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.
I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).
However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.
VB:
Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count
[Code] ......
I am in the unfortunate position of having to run about 900 correlations with Excel as my only option. Normally this would be fine, but in this case, I need the Pearson's test of significance. The only way I know how to obtain this in Excel is through using a regression, which is incredibly time-consuming. Does anyone have a suggestion how to make the best use of time?
View 11 Replies View RelatedI am using excel to do a monte carlo analysis of a process. Until now, all my distributions have been uncorrelated, but from now I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal. I don't have a stats background, so how to implement this. Producing the two distributions is no problem, but I would now like to be able to alter them or the original formulae so they are correlated properly. If it would greatly simplify the process, the lognormal distribution could be replaced by a normal, but not the weibull.
The formulae I'm using for each is:
I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.
View 4 Replies View RelatedI have a table that I add rows to via a macro. I am trying to add correlation matrices that find the last row of the table, and include a certain number of rows above that to create a rolling correlation. For example, if I want the 2 week correlation, I would like the last 10 rows of data, but if I add another row, this formula should move down to incorporate the new row and deselect the now 11th day preceding. I do this manually but it is very time consuming.
View 9 Replies View RelatedI am now analysing the ordered correlations of products in a warehouse.For example, I have 16 products (product 1 to product 16) in my warehouse. During last month, there were totally 391 orders (order 1 to order 391). If a product is requested in a ordered, an "X" will be marked as per attached sheet.
Now my goal is to figure out which pair (or even triple) of products are ordered/requested at the same time (or in the same order) for the last month. I intend to use the analasys result to move the more ordered pair/triple products to stay near to each other at the warehouse so it will save the time for picking.
How to do that analysis?
I have a particular issue - i am trying to create a rolling correlation.
lets say you have two variables for 3 different months:
Month1 a x
Month2 b y
Month3 c z
correlation will correlate a-c with t-v
however, come next month - i have to manually drag the ranges for a rolling three month correlation:
Month1 a t
Month2 b u
Month3 c v
Month4 d w
correlation will now correlate b-d with u-w for a the last three months.
I want to automate this as I know what the month is, I can't seem to return the last three months and correlate the data.
I have tried to do this with the database functions, but I think that they break the array relationship of b-d and u-w in the correlation function.
I'm trying to Pearson Ranks correlation table. This is the formula I'm
using:
=PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230)
This is placed in cell B3. When I copy the formula over to DT3 the formula
correctly adjusts the B in the second part of the formula all the way to DT
and still references the values in the B column.
But I want to now copy this down to C4, D5, E6 and then over to DT for each
so that it correctly makes the correlation table.
I wonder if you think it can be possible to constraint the RAND function so that it gives off its result commensurate with the result of another RAND function with certain probability N? For example,
Say there are two random functions and the first generates 0,5667 the correlation is at 0,5 so there is a 50% probability that the second random function will generate the result very close to it, say, 0,6012….Do you think the second random function can be tweaked so that it performs in this manner….when there is a 100% correlation – the results of both random functions will agree…when it is 80% it will be very close…..do you think this can be done with NORMINV?
see attached pivot table... I believe that there is a statistical relationship between column B & C (together, i.e. their combined effect) upon column D. i.e. (B & C) - have some relationship to effect D.
(1) What formula would I use to measure this correlation?
I have a list with hundreds of customers. Each customer has two measures (i won't bore you with how they are calculated)
1) % Coverage by Salesman
2) % Of Opportunity Won
What tips and tricks can you give me to take my analysis to the next level? The example is a very limited sample and the data was made up using a formula so correlated very well, i've chucked in a couple of anamlous results to make it a bit more realistic.
One of the main things i would like to see is a correlation "score". I suppose if the two series correlated perfectly then the score would be 100%. The less they correlate, then the lower this score gets....
In business terms, if i can run this rule over thousands of customers and get a good correlation - then it becomes a conversation with the salesman to show them that doing those pesky, boring coverage calls has a measurable impact on sales...and here is the number to prove it...
How I can interpret this data?
I am looking for the best way ( or choice of ways) to display the correlation between 2 lists of results.
I have a column of failure percentage rates for engineers that I want to compare against a second column of their compliance rate with a process. There are two levels also. The first set shows the good performers and their compliance, the second showing the poor performers.
My goal is to see whether their compliance with the process has any bearing on their failure rates. The full lists contain data on approximately 400 engineers in each category
(if the the numbers below are not in a proper format, I tried to download the excel converter thingy but the link failed)
faliure rate
Compliance
3.95%
91.40%
[Code]....
I have the monthly data stores column by column, e.g. Column B for Jan 07, Col C for Feb 07 etcetc, so every month I have a new column added to the end then I have some statistical analysis table which calculates such as annual return, standard deviation etcetc
How can I make a button which I can click then it can update to replace the formular with the latest column? for example, =CORREL(B2:B10, G2:G10), col B contains Feb 07 data and G contains June 07, let's say I add another column for July 07 which will be column H and I would like by clicking the button then all formula with col G will be replaced by col H?
I am facing a problem with WorksheetFunction.Correl
I got information, that matching class properties of Worksheetfunction is impossible.
This is my first experience with macro's and I hardly know how to deal with this problem.I got some excel file from my friend and tried to do this in the same way.
Macro was supposed to calculate the Pearson's coefficient of correlation for orders from 1 to 100 of autocorrelation in order to calculate Q Box-Pierce statistic.Data is prices of stocks for some bank and correlation coefficient is calculated for simple returns on stocks.
I have an array of data (A,B,C,D,...,H) and a correlation/relationship data table (asymmetric data).
Out of the array data, i need to make a combination of 3 data out of 8 AND input the relationship data according to the combination.
I found a problem to take the reference from correlation data.
to help you understand my issue, i attached the file with this posting.
I am calculating multiple Correlations for long lists of data. I want the correlation based on the value in a given column. In the below example I would like a different correlation for each value in colA and the correlation will be between colC and colD. ColB lists the different people whose data are used for the correlations. In actuality there are 30 or more people for each test. In every case ColC is the same for a given value of ColB (although I doubt that matters). The number of people taking each test varies. I would like a correlation of ColC an ColD for each value of ColA.
Simplified version of my data
ColA ColB ColC ColD
Test1 Fred .75 1
Test1 Max .80 0
Test1 Sara .90 1
Test2 Fred .75 1
Test2 Max .80 1
Test2 Sara .90 0
....
Test100 Fred .75 1
Test100 Max .80 1
Test100 Sara .90 1
Any existing UDF that will take two ranges of Prices (for stocks) but get their respective returns to then get the correlation of that?
View 4 Replies View RelatedI have a workbook entitled "Correlation Matrix". I download data from my data provider and then update it in excel each night. I have several worksheets in the workbook, one called "MCC". Today I made a copy of MCC, named MCC2, to experiment, then deleted it. Now, after I update my data in Excel it gives me the msg, "We can't open the source file '(Path)[Correlation Matrix.xlsx]MCC2'".
The data updates fine and all values are correct in the worksheets. I've tried closing and reopening the file, closing and reopening excel, but I'm still getting this msg when updating the data.
i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33"Bad";IF(D4:D33"Bad";B4:B33)));IF(B4:B33"Bad";IF(D4:D33"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.
22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
I trying to populate cells with a correlation function using VBA, I have set up dynamic references/arguments for the fuction to take on however I just can't get it to work. Currently instead of populating the formula into the cell, it's just populates it as a string based on what's entered below =correl(Ystart &":"& yend, xstart&'":"'& xend").
FYI - The correl function takes on two ranges,. I've predefined these below.
Sub testing()
lastcell = ActiveSheet.Range("c575").End(xlUp).Row
firstcell = ActiveSheet.Range("c1").End(xlDown).Row
Ystart = ("b" & firstcell)
yend = ("b" & lastcell)
xstart = ("c" & firstcell)
xend = ("c" & lastcell)
Range("c575:c580").Formula = "=correl(Ystart &":"& yend, xstart&'":"'& xend")"
'this works
'Range(xstart, xend).Select
End Sub
I have a very large spreadsheet, a few columns and rows I attach..
in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.
What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down
see attached sheet