Calculate the Correlation of two arrays where each array is disjointed2019 Community Moderator ElectionIs it possible to force Excel recognize UTF-8 CSV files automatically?How to sum correlation, or, calculate correlation of disjointed variablesCalculate Correlation Coefficient Between 2 Variables Grouped by a 3rd VariableCorrelation function with addition and subtraction in the arraysDifferentiate Between Zero and Blank in ExcelCORREL Function only returns values of 0, 1, or -1 on varied datasetExcel Automatic row insertion between two arrayscalculate critical value for The Spearman Rank Correlation CoefficientFinding max value of a loop with VBAHow can I calculate correlation within sub-groups of my sample in Excel
Named nets not connected in Eagle board design
The need of reserving one's ability in job interviews
How spaceships determine each other's mass in space?
Giving a talk in my old university, how prominently should I tell students my salary?
Genitives like "axeos"
Ultrafilters as a double dual
Where do you go through passport control when transiting through another Schengen airport on your way out of the Schengen area?
I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?
How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?
Under what conditions would I NOT add my Proficiency Bonus to a Spell Attack Roll (or Saving Throw DC)?
How do we objectively assess if a dialogue sounds unnatural or cringy?
Why won't the strings command stop?
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
Split a number into equal parts given the number of parts
Affine transformation of circular arc in 3D
Is being socially reclusive okay for a graduate student?
Is there a math equivalent to the conditional ternary operator?
Are Wave equations equivalent to Maxwell equations in free space?
Quitting employee has privileged access to critical information
PTiJ: How should animals pray?
Are angels creatures (Mark 16:15) and can they repent (Rev 2:5 and Rom 8:21)
Align equations with text before one of them
Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?
Why can't we use freedom of speech and expression to incite people to rebel against government in India?
Calculate the Correlation of two arrays where each array is disjointed
2019 Community Moderator ElectionIs it possible to force Excel recognize UTF-8 CSV files automatically?How to sum correlation, or, calculate correlation of disjointed variablesCalculate Correlation Coefficient Between 2 Variables Grouped by a 3rd VariableCorrelation function with addition and subtraction in the arraysDifferentiate Between Zero and Blank in ExcelCORREL Function only returns values of 0, 1, or -1 on varied datasetExcel Automatic row insertion between two arrayscalculate critical value for The Spearman Rank Correlation CoefficientFinding max value of a loop with VBAHow can I calculate correlation within sub-groups of my sample in Excel
I have data similar to the following in Excel:
A B
1 1,287.90 221,300
2 0 0
3 1,828.93 64,080
4 417.81 0
5 397.37 76,529
6 1,371.69 0
7 1,316.81 50,761
8 1,828.93 28,645
I want to calculate the correlation coefficient of these two arrays where there are non-zeros. I want to do this without copying the data to a separate sheet with the zeros removed. I have tried this for example:
CORREL(A1,A3,A5, A7:A8, B1,B3,B5, B7:B8) but this does not work. Any ideas
excel
asked yesterday
Daniel WyattDaniel Wyatt
397
add a comment |
I have data similar to the following in Excel:
A B
1 1,287.90 221,300
2 0 0
3 1,828.93 64,080
4 417.81 0
5 397.37 76,529
6 1,371.69 0
7 1,316.81 50,761
8 1,828.93 28,645
I want to calculate the correlation coefficient of these two arrays where there are non-zeros. I want to do this without copying the data to a separate sheet with the zeros removed. I have tried this for example:
CORREL(A1,A3,A5, A7:A8, B1,B3,B5, B7:B8) but this does not work. Any ideas
excel
asked yesterday
Daniel WyattDaniel Wyatt
397
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?
– Chronocidal
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result ofCORREL().
– jsheeran
yesterday
add a comment |
I have data similar to the following in Excel:
A B
1 1,287.90 221,300
2 0 0
3 1,828.93 64,080
4 417.81 0
5 397.37 76,529
6 1,371.69 0
7 1,316.81 50,761
8 1,828.93 28,645
I want to calculate the correlation coefficient of these two arrays where there are non-zeros. I want to do this without copying the data to a separate sheet with the zeros removed. I have tried this for example:
CORREL(A1,A3,A5, A7:A8, B1,B3,B5, B7:B8) but this does not work. Any ideas
excel
asked yesterday
Daniel WyattDaniel Wyatt
397
I have data similar to the following in Excel:
A B
1 1,287.90 221,300
2 0 0
3 1,828.93 64,080
4 417.81 0
5 397.37 76,529
6 1,371.69 0
7 1,316.81 50,761
8 1,828.93 28,645
I want to calculate the correlation coefficient of these two arrays where there are non-zeros. I want to do this without copying the data to a separate sheet with the zeros removed. I have tried this for example:
CORREL(A1,A3,A5, A7:A8, B1,B3,B5, B7:B8) but this does not work. Any ideas
excel
excel
asked yesterday
Daniel WyattDaniel Wyatt
397
asked yesterday
Daniel WyattDaniel Wyatt
397
edited yesterday
Daniel Wyatt
asked yesterday
Daniel WyattDaniel Wyatt
397
asked yesterday
Daniel WyattDaniel Wyatt
397
asked yesterday
Daniel WyattDaniel Wyatt
397
397
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?
– Chronocidal
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result ofCORREL().
– jsheeran
yesterday
add a comment |
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?
– Chronocidal
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result ofCORREL().
– jsheeran
yesterday
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (
CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?– Chronocidal
yesterday
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (
CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?– Chronocidal
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result of
CORREL().– jsheeran
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result of
CORREL().– jsheeran
yesterday
add a comment |
1 Answer
1
active
oldest
votes
Correct me if I'm wrong, but I think you don't have to do nothing:
As you can see the correlation result is the same. Did I misunderstand your question?
Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:
=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)
Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.
answered yesterday
PsplPspl
786216
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a-have zeros on your question?
– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55022298%2fcalculate-the-correlation-of-two-arrays-where-each-array-is-disjointed%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Correct me if I'm wrong, but I think you don't have to do nothing:
As you can see the correlation result is the same. Did I misunderstand your question?
Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:
=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)
Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.
answered yesterday
PsplPspl
786216
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a-have zeros on your question?
– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
add a comment |
Correct me if I'm wrong, but I think you don't have to do nothing:
As you can see the correlation result is the same. Did I misunderstand your question?
Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:
=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)
Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.
answered yesterday
PsplPspl
786216
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a-have zeros on your question?
– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
add a comment |
Correct me if I'm wrong, but I think you don't have to do nothing:
As you can see the correlation result is the same. Did I misunderstand your question?
Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:
=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)
Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.
answered yesterday
PsplPspl
786216
Correct me if I'm wrong, but I think you don't have to do nothing:
As you can see the correlation result is the same. Did I misunderstand your question?
Update: From your comment I suppose the dashed cells on your question may represent zeros and you want to remove those values from the result. If that's the case you could place the formula:
=CORREL(IF(A1:A8*B1:B8<>0;A1:A8);B1:B8)
Don't forget to enter the formula by Ctrl + Shift + Enter to place the brackets.
answered yesterday
PsplPspl
786216
edited yesterday
answered yesterday
PsplPspl
786216
answered yesterday
PsplPspl
786216
answered yesterday
PsplPspl
786216
786216
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a-have zeros on your question?
– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
add a comment |
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a-have zeros on your question?
– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
If you remove the zero values so it is blank then yes you are right. However, I am not able to do this. I need to calculate the correlation whilst the zeros are still in the data
– Daniel Wyatt
yesterday
Do you mean that your cells with a
- have zeros on your question?– Pspl
yesterday
Do you mean that your cells with a
- have zeros on your question?– Pspl
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Yes that works. Thank you so much. (I'm assuming you want commas between your arguments rather than semicolons?)
– Daniel Wyatt
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Actually, since I'm from Portugal, I really want semicolons... Don't forget to accept my answer!
– Pspl
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
Aaaaah ok. No problem
– Daniel Wyatt
yesterday
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55022298%2fcalculate-the-correlation-of-two-arrays-where-each-array-is-disjointed%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you tried using [Ctrl]+[Shift]+[Enter] to make it an Array Formula? And / or, entering every cell manually (
CORREL(A1,A3,A5,A7,A8,B1,B3,B5,B7,B8)) instead?– Chronocidal
yesterday
What's your expected output? I've just tried inserting rows with one or two blank values and it didn't affect the result of
CORREL().– jsheeran
yesterday