1. B

    Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula

    Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula 1. In a single formula, I am trying to get the Rank (Spearman) correlation between two arrays. One of the arrays contains non-adjacent values. I can get the Pearson correlation with this formula...
  2. K

    VBA Correlation matrix with Paramarray

    Hello, I have an excel sheet with multiple columns and I need to create correlation matrix for some columns that are not necessarily next to each other. I tried to replace the range with paramarray in an UDF, but didn't work and I have no idea about how to solve this problem. Function...
  3. H

    How do I create Simularity/Correlation Matrix using Non-Numeric Data

    Hello, I am trying to create a matrix of constituents in our database comparing their Relationships to other constituents. I have a list of constituents, and each one has a list of people they have relationships with (represented here are constituent IDs for people attached to their record as a...
  4. S

    How to analyse correlations?

    Price change % is my column of interest. I would like to measure whether or not there are correlations between Price change % and any of the other columns. What visualisations would be best for this? What I've tried so far is creating pivot tables and grouping by Production year, Category...
  5. G

    Standard deviation of correlation matrix

    I am looking to get the standard deviation of each correlation coefficient in a matrix. The matrix is complete (as in each pairwise correlation in included twice) but i want the standard deviation including only each coefficient once. The matrix will vary in size and can be quite large so i...
  6. L

    Correlation between two columns, ignoring errors

    Hi all, I want to check the correlation between two columns (column B and I), taking out all the values that give an error. Furthermore if a certain nth value in column B (I) gives an error, the corresponding nth value in column I (B) should also be ignored. I did it this way but it gaves a...
  7. B

    Predicting sales figures from quotes

    Hi all Probably quite a basic question for you experts on here but I'm trying to solve the following problem: I have values of quotes and values of sales for c.30 weeks worth of data: Example, Week 1 Quotes: £3m Sales: £1m Week 2 Quotes: £4m Sales: £2m etc...
  8. M

    Fill out 'complete correlation table"

    Hi All... I need the correlation table to be completely fill out so that I can ADD the full columns to do a few functions. Is there a way to copy and transpose the numbers over? Thxs
  9. C

    Dynamic Correlation (possible PivotTable)

    I am trying to figure out a way to do dynamic correlation between variables by choosing start and end date, (Vertical Column A), and doing correlation between 2 different columns of values. The kicker is there is also a column of identifiers in Column B. So i'd like to select correlation between...
  10. S

    worksheetfunction.index array form

    I am working with the lines: ArrX = Application.WorksheetFunction.Index(X, 0, 2) ArrTemp = Application.WorksheetFunction.Index(TempSample, 0, 2) DistanceMeasure = (1 - Application.WorksheetFunction.Correl(ArrX, ArrTemp)) * 100 I would like to be able to run a simple correlation using...
  11. W

    Correlation using lookups

    <tbody> A - Month B - Date C - Period D - Price A E -Price b =date(year(B2),month(B2),1) 01/01/2010 1 32.22 33.01 =date(year(B3),month(B3),1) 01/01/2010 2 30.72 31.78 =date(year(B4),month(B4),1) 01/01/2010 3 30.91 30.65 =date(year(B5),month(B5),1) 01/01/210 4 25.91...
  12. Manuel Cavero

    How to calculate the Correlation of multiple Dictionaries?

    Hello everyone: I have some dictionaries with values, and I need to calculate the correlation between the values of dictionaries. Let's see the code: Sub Test() Dim Dic1 as New Scripting.Dictionary Dim Dic2 as New Scripting.Dictionary Dim Dic3 as New Scripting.Dictionary Dim Dictionaries as...
  13. S

    Aggregating addresses by subdivision while keeping owner name intact

    I have a list of just over 6,200 names and addresses that I need to separate into their respective community subdivision, but I need to keep the owners names with those addresses. This is in order to add postal/zip codes and cities to the addresses. Is there a way to do this? For example, I need...
  14. J

    Modified Correlation Matrix output

    I have a UDF for correlation matrix (enclosed); can it be modified to replace the non-result 0's (zeros) with "n/a"? For example, When I use the function I get a 1-sided correlation matrix for -- - data: <tbody> x1 x2 x3 x4 1.0000 1.0000 2.0000 4.0000 4.0000 3.0000 4.0000...
  15. M

    Correlation between two sets of categorical variables

    Hi, Been researching this for a while and can't find an example of how to set this up with Excel formula. I have two sets of categorical variable (ie yes/no answers) and I want to find the correlation between them Example: <tbody> Filled out notes field Did not fill out notes field...
  16. J

    Semipartial Correlation UDF

    I need help in modifying the appended Partial Correlation UDF to Semipartial Correlation UDF. A note on Semipartial Correlation (and Partial) can be found at The Partial Correlation UDF is: Function pCorrel(R) Dim iRows As Integer...
  17. B

    Next number in Sequence or correlation between 3 sets of sequences

    I'm looking for a formula to find the correlation from one number to the next and also the next number in the sequence. The formula should be for each independent column. Additionally, the three columns values are from one entity just three different measurements. <style...
  18. R

    Correlation Meaning of Survey Data

    Hey, So I calculated a survey average based off of 10 questions asked scored 1-10. I have 2,000 surveys and took the correlation of each individual score vs the total average scores. What does this tell me? What do the results mean? Is this a way to measure impact? Example: Total Score...
  19. O

    Weighting Correlation Co-Efficients to measure performance

    Hi, I have some interesting data based on soccer that I am trying to use to measure performance based on the correlation coefficients. I have data for each player in the English Premier League that shows how many goals they have scored and with this range of other data that contributes to...
  20. G

    Generate correlated normal random series

    Hi, I'm hoping someone can help me with the following. In excel I'd like to simulated return series for 2 correlated assets, X&Y, with the following properties, and using a normal distribution: Mean X:0.15%, Std Dev X:7.25% Mean Y:0.55%, Std Dev Y: 5.5% Correlation of X,Y: 0.66 Thanks

Some videos you may like

This Week's Hot Topics