| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correlation analysis
HELP!
I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
Just use the CORREL() function.
CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
Is there a function in Excel that automatically queries just the most recent
20 occurances or do I have to manually adjust the parameters each day? Thanks! Todd "Sean Timmons" wrote: Just use the CORREL() function. CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
=CORREL(INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0)-20,1)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0),1)),INDIR ECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0)-20,2)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0),2)))
This assumes your values are down columns A and B with headers in row 1. Change A's to your first array column name, B's to your 2nd array column name. Also where I have: A:A,0),1 Change to your column name and the number of the column (i.e. - Column D would be 4 instead of 1) Same with the 2nd formula. If you use rows, just change A and B for row #'s "todd012976459210" wrote: Is there a function in Excel that automatically queries just the most recent 20 occurances or do I have to manually adjust the parameters each day? Thanks! Todd "Sean Timmons" wrote: Just use the CORREL() function. CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
Great!! Thank you very much. Let me dive into this and play with it and see
if I can get it to work. "Sean Timmons" wrote: =CORREL(INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0)-20,1)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0),1)),INDIR ECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0)-20,2)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0),2))) This assumes your values are down columns A and B with headers in row 1. Change A's to your first array column name, B's to your 2nd array column name. Also where I have: A:A,0),1 Change to your column name and the number of the column (i.e. - Column D would be 4 instead of 1) Same with the 2nd formula. If you use rows, just change A and B for row #'s "todd012976459210" wrote: Is there a function in Excel that automatically queries just the most recent 20 occurances or do I have to manually adjust the parameters each day? Thanks! Todd "Sean Timmons" wrote: Just use the CORREL() function. CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
I don't think you need to make it that complicated nor using volatile
functions as long as there will be no empty cell in-between =CORREL(INDEX(A2:A10000,COUNT(A2:A10000)):INDEX(A2 :A10000,COUNT(A2:A10000)-19),INDEX(B2:B10000,COUNT(B2:B10000)):INDEX(B2:B10 000,COUNT(B2:B10000)-19)) -- Regards, Peo Sjoblom "todd012976459210" wrote in message ... Great!! Thank you very much. Let me dive into this and play with it and see if I can get it to work. "Sean Timmons" wrote: =CORREL(INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0)-20,1)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0),1)),INDIR ECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0)-20,2)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0),2))) This assumes your values are down columns A and B with headers in row 1. Change A's to your first array column name, B's to your 2nd array column name. Also where I have: A:A,0),1 Change to your column name and the number of the column (i.e. - Column D would be 4 instead of 1) Same with the 2nd formula. If you use rows, just change A and B for row #'s "todd012976459210" wrote: Is there a function in Excel that automatically queries just the most recent 20 occurances or do I have to manually adjust the parameters each day? Thanks! Todd "Sean Timmons" wrote: Just use the CORREL() function. CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correlation analysis
OK! Thanks, I'll try it.
"Peo Sjoblom" wrote: I don't think you need to make it that complicated nor using volatile functions as long as there will be no empty cell in-between =CORREL(INDEX(A2:A10000,COUNT(A2:A10000)):INDEX(A2 :A10000,COUNT(A2:A10000)-19),INDEX(B2:B10000,COUNT(B2:B10000)):INDEX(B2:B10 000,COUNT(B2:B10000)-19)) -- Regards, Peo Sjoblom "todd012976459210" wrote in message ... Great!! Thank you very much. Let me dive into this and play with it and see if I can get it to work. "Sean Timmons" wrote: =CORREL(INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0)-20,1)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<""),A2:A1000),A:A,1,0),A:A,0),1)),INDIR ECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0)-20,2)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<""),B2:B1000),B:B,1,0),B:B,0),2))) This assumes your values are down columns A and B with headers in row 1. Change A's to your first array column name, B's to your 2nd array column name. Also where I have: A:A,0),1 Change to your column name and the number of the column (i.e. - Column D would be 4 instead of 1) Same with the 2nd formula. If you use rows, just change A and B for row #'s "todd012976459210" wrote: Is there a function in Excel that automatically queries just the most recent 20 occurances or do I have to manually adjust the parameters each day? Thanks! Todd "Sean Timmons" wrote: Just use the CORREL() function. CORREL(array1,array2) Just grab the necessary arrays to accomplish your goal and you are solid! "todd012976459210" wrote: HELP! I need to do a correlation analysis of mutiple variables against an observable daily event. Can someone profiicient in Excel and statistics guide me? Specifically, I have 10 variables whose correlation I want to measure against a daily observation, both individually and collectively. I also want to mearsure the correlation for all available samples, but also for just the most recent 20. Please, can anyone help me accomplish this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| correlation | Excel Worksheet Functions | |||
| How do I set up a correlation formula? | Excel Worksheet Functions | |||
| In Regression Analysis, How do you get a Correlation Matrix to be. | Excel Discussion (Misc queries) | |||
| How do I set up a correlation table? | Excel Discussion (Misc queries) | |||
| Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) | |||
Linear Mode
