Home

Use of ranges in an array formula

%3CLINGO-SUB%20id%3D%22lingo-sub-790743%22%20slang%3D%22en-US%22%3EUse%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790743%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20how%20could%20the%20following%20be%20done%20in%20an%20array%20formula%3A%3C%2FP%3E%3CP%3E%3D(CORREL(B1%3AG1%2CB1%3AG1)%26gt%3B0.7)%2B(CORREL(B1%3AG1%2CB2%3AG2)%26gt%3B0.7)%2B%20...%20%2B%20(CORREL(B1%3AG1%2CB100%3AG100)%26gt%3B0.7)%3C%2FP%3E%3CP%3EThe%20result%20should%20be%20an%20integer%20between%201%20and%20100%20(the%20first%20term%20will%20be%201).%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-790743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793193%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793193%22%20slang%3D%22en-US%22%3EI'd%20stick%20with%20placing%20the%20original%20CORREL%20function%20in%20a%20column%20from%20row%202-100%20and%20adding%20a%20SUM%20at%20the%20bottom.%20This%20is%20likely%20to%20perform%20better%20as%20well.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793257%22%20slang%3D%22en-US%22%3ERE%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793257%22%20slang%3D%22en-US%22%3EFor%20getting%20all%20the%20correlations%2C%20a%20matrix%20of%20100x100%20correlations%20would%20be%20required.%20It%20gets%20awkward%20when%20I%20want%20to%20sift%20the%20input%20data%20by%20one%20column%20requiring%20another%20100x100%20matrix%20and%20compare%20the%20sums%20with%20the%20first%20sums%2C%20etc.%20I%20have%20the%20impression%20that%20it%20is%20not%20possible%20to%20include%20ranges%20in%20an%20array%20formula.%20I%20have%20tried%20all%20kind%20of%20solutions%20but%20could%20not%20get%20it%20to%20work.%20If%20there%20is%20a%20solution%20I%20would%20like%20to%20try%20it%20even%20if%20it%20is%20not%20optimal.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793284%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20getting%20all%20the%20correlations%2C%20a%20matrix%20of%20100x100%20correlations%20would%20be%20required.%20It%20gets%20awkward%20when%20I%20want%20to%20shift%20the%20input%20data%20by%20one%20column%20requiring%20another%20100x100%20matrix%20and%20compare%20the%20sums%20with%20the%20first%20sums%2C%20etc.%20I%20have%20the%20impression%20that%20it%20is%20not%20possible%20to%20include%20ranges%20in%20an%20array%20formula.%20I%20have%20tried%20all%20kind%20of%20solutions%20but%20could%20not%20get%20it%20to%20work.%20If%20there%20is%20a%20solution%20I%20would%20like%20to%20try%20it%20even%20if%20it%20is%20not%20optimal.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793318%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793318%22%20slang%3D%22en-US%22%3EHave%20you%20got%20some%20sample%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793363%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20trying%20things%20out%20I%20used%20%3Drand()%20in%20all%20cells%20of%20range%20B1%3AG100%2C%20copied%20and%20paste%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793395%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20of%20ranges%20in%20an%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793395%22%20slang%3D%22en-US%22%3EI've%20tried%20a%20couple%20of%20variations%20but%20it%20doesn't%20seem%20to%20be%20possible%20in%20just%20one%20array%20formula.%3C%2FLINGO-BODY%3E
Jon_Arts
New Contributor

Hello, how could the following be done in an array formula:

=(CORREL(B1:G1,B1:G1)>0.7)+(CORREL(B1:G1,B2:G2)>0.7)+ ... + (CORREL(B1:G1,B100:G100)>0.7)

The result should be an integer between 1 and 100 (the first term will be 1). 

Thank you

5 Replies
I'd stick with placing the original CORREL function in a column from row 2-100 and adding a SUM at the bottom. This is likely to perform better as well.

@Jan Karel Pieterse 

For getting all the correlations, a matrix of 100x100 correlations would be required. It gets awkward when I want to shift the input data by one column requiring another 100x100 matrix and compare the sums with the first sums, etc. I have the impression that it is not possible to include ranges in an array formula. I have tried all kind of solutions but could not get it to work. If there is a solution I would like to try it even if it is not optimal. 

Have you got some sample data?

@Jan Karel Pieterse 

For trying things out I used =rand() in all cells of range B1:G100, copied and paste values.

Highlighted
I've tried a couple of variations but it doesn't seem to be possible in just one array formula.
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies