Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula
HI,
I´d like to "compact" the following column in a single cell value using array formulas, if that is possible. The columa A is filled with results of a certain function, and column B has the Pearson correlation of that column in this way. b1: =pearson(a1:a5,a2:a6) b2: = pearson(a1:a5,a3:a7) .. .. b100=pearson(a1:a5,a101:a105) Then, what I really need is the max of those b cells, so c1=max(b1:b100). The question is, can I avoid the creation of column b, with something like : c1={max(pearson(a1.....))}, if it is possible I can´t find the right sintax... (also posted in microsoft.public.excel,not sure what is the right place) Thanks in advance for your time Mika |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula
I don't hink it's possible. All arrays in an array formula must contain the
same number of rows or columns, while your formula would have to contain contain both 5-element arrays (a1:a5, a2:a6, etc.) and a 100-element array (a1:a100). "Mika" wrote: HI, I´d like to "compact" the following column in a single cell value using array formulas, if that is possible. The columa A is filled with results of a certain function, and column B has the Pearson correlation of that column in this way. b1: =pearson(a1:a5,a2:a6) b2: = pearson(a1:a5,a3:a7) .. .. b100=pearson(a1:a5,a101:a105) Then, what I really need is the max of those b cells, so c1=max(b1:b100). The question is, can I avoid the creation of column b, with something like : c1={max(pearson(a1.....))}, if it is possible I can´t find the right sintax... (also posted in microsoft.public.excel,not sure what is the right place) Thanks in advance for your time Mika |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula
Hi,
Not sure I understand your answer... let's see, what I'm asking is if it is possible to convert this to an array formula: c1: =MAX(pearson(a1:a5,a2:a6),pearson(a1:a5,a3:a7),... ..,pearson(a1:a5,an:an +4)) so, you see, I don't want to type all the (....) terms in between (I can't actually) hope is clearer now.. Thanks Mika |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula
It's curious how this doesn't work:
=PEARSON(A1:A5,INDIRECT("A"&ROW(2:101)&":A"&ROW(6: 105))) It gets stuck at the INDIRECT call, not the pearson part. On Sep 10, 8:46 am, hmm wrote: I don't hink it's possible. All arrays in an array formula must contain the same number of rows or columns, while your formula would have to contain contain both 5-element arrays (a1:a5, a2:a6, etc.) and a 100-element array (a1:a100). "Mika" wrote: HI, I´d like to "compact" the following column in a single cell value using array formulas, if that is possible. The columa A is filled with results of a certain function, and column B has the Pearson correlation of that column in this way. b1: =pearson(a1:a5,a2:a6) b2: = pearson(a1:a5,a3:a7) .. .. b100=pearson(a1:a5,a101:a105) Then, what I really need is the max of those b cells, so c1=max(b1:b100). The question is, can I avoid the creation of column b, with something like : c1={max(pearson(a1.....))}, if it is possible I can´t find the right sintax... (also posted in microsoft.public.excel,not sure what is the right place) Thanks in advance for your time Mika- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula
Yes Illia very curious, even this "simple" case does not work:
=PEARSON(A1:A5,INDIRECT("A"&ROW(2:2)&":A"&ROW(6:6) )) (don't need array formula here, but still doesn't work) which should be the equivalent of =pearson(a1:a5,a2:a6), anyway I think you shown the way and maybe somebody else can help to puzzle this out.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array formula | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Need Help With ARRAY Formula | Excel Worksheet Functions | |||
Array formula help | Excel Discussion (Misc queries) |