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

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

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

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 -

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....