3D Array Formula - Is it Possible?

[krame00]

Before I waste any more time on this - is it possible for an Array formula to be 3D? I am having no success, and I have heard it is - if you know how to do it right...

I tried searching the forum, but got too many hits to weed though - so if anyone has a quick answer that would be helpful.

Thanks!

Khana

 

[Exceljeanie]

Hi all,
my idea was stupid, it works only with empty cells not with zeros and has some more problems - should think first than post

Sorry

 

[pgc01]

Hi

Playing with Peter's idea

=SUM(Sheet1:Sheet5!A1)/SUMPRODUCT(FREQUENCY(Sheet1:Sheet5!A1,{-1E-307,0})*(ROW(1:3)<>2))

You can test the denominator to avoid DIV/0.

Hope this helps
PGC

 

[Domenic]

Hi

Playing with Peter's idea

=SUM(Sheet1:Sheet5!A1)/SUMPRODUCT(FREQUENCY(Sheet1:Sheet5!A1,{-1E-307,0})*(ROW(1:3)<>2))

You can test the denominator to avoid DIV/0.

Hope this helps
PGC

Hi PGC,

Very interesting. I didn't know that FREQUENCY would accept a 3D reference for the data array. And apparently, the same thing applies for the bins array. For the denominator, however, I think the following might suffice...

INDEX(FREQUENCY(Sheet1:Sheet5!A1,0),2)

 

[pgc01]

Hi Domenic

You are right if you only consider positive numbers. My formula averages all the numbers, positive and negative, excluding the zeros. If the problem deals only about positive numbers your denominator is indeed preferable.

Best regards
PGC

 

[Domenic]

Yes, I assumed that only numbers greater than zero would be averaged. Maybe I shouldn't have made that assumption. However, my understanding is that an average is meaningless if the calculation excludes zero's but includes negative numbers, hence my usual assumption in these cases. I don't think I've seen any cases where an average of positive and negative numbers excludes zero's.